Search Results

table formula - given a Table1 table name for the above table: =INDEX(Table1[Category],MATCH([@Id],Table1[Id],0)) Excel likes data that's organized in rows and columns; if you have 2 levels of headings …

There are many iterative structures in VBA, but I think the one that would be most useful here is For...Next...Step; in your case, looks like Step should be 4, looping from 7 to 87. All of your code …

Doing Copy+Paste in your IDE should raise a big red flag and then there should be a neural inhibitor that prevents your left hand from doing it, causing your right hand to move your mouse further down …

Not relevant to your algo, but you shouldn't be using "" like this; use the vbNullString language constant instead, which consumes 0 memory (as opposed to "" which has the overhead of a string). As f …

So, moving on: Bring up a FileDialog, open any Excel file that the user selects. Activate the 3rd sheet in the 2nd workbook (the one we just opened?) and keep a reference to the active sheet. …

Then I'd try to reduce the number of Excel worksheet reads, if that's at all possible. …

The problem is that you're catching an exception that should be avoidable: reading an empty cell isn't something that should be exceptional in Excel after all. … Don't rely on exceptions when you can avoid them, especially with Excel interop. …

I think that's [mis|ab]using VBA: Excel itself is very good at dealing with assigning cell values based on other cells' values. …

'No error handling should be needed Except, you never really know what assumptions you're making without realizing. Better safe than sorry. For example, here's where your procedure might blow up: …

I'm aware that making all the declarations line up like this isn't an efficient use of time, but since it's already been done, I'm not about to go and spend more time deliberately undoing it. Tha …

Nice code, I like what you've been smoking. An alternative (better?) name for the function could have been Legalize, too. You could easily modify it to optionaly verify existing names (e.g. Optional …

"CodeName" is a property of all sheet objects in Excel VBA; there's a global object reference pointing to these, readily available for you to use - no need to fetch it from WB.Sheets collection, which … Otherwise Excel will look "frozen" when it's actually completely responsive, just not redrawing itself... because you told it not to. Don't assume things won't blow up. They always do. …

hide the status bar at any time - this feels rather surprising, especially since you could instead be using it to convey some "Please wait..." message to the user so they know your code is running and Excel …

country i = i + 1 You need the values from 3 cells - store them in local variables, and reuse variables instead of accessing the worksheet over and over: accessing a worksheet is the slowest thing Excel-VBA …

This "guard clause" does not need to be a block: If workingRange Is Nothing Then Exit Sub End If Inlining the Exit Sub makes it clearer that it's intended to be a "quick sanity check" and not …