Grabbing messy data with VBA Arrays
Let’s say Sheet1 (codename) has the Commercial Invoice (see below and the blue shaded bits is what we want) which has the static and transactional bits and pieces of data we want to record to our ongoing data in Sheet2 together with previous invoice data (see further below in the green Table format). By the way and as I have said in previous posts, any good analyst will want the datasets stored in a database format with transactions down the page and dimensions of that data across. In less technical terms it should be long and skinny! This is so you can make use of more efficient Excel tools like Pivot Tables, Pivot Charts and lookup formulas.
OK so we can add a button and this VBA would do the job of grabbing this data for us. Now note all the data we want to collect is in Column P except for one bit which I cater for towards the end of the code. By the way when I code, I use sheet codenames so it’s more robust and not mucked up by a user changing the Tab name.
Option Explicit Option Base 1 Sub GrabData() Const SOURCE_COL = 16 'Which is column P where all but 1 piece of our data is on the Commercial Invoice Dim i As Integer Dim iSource As Variant Dim iTarget As Variant Dim newrow As Long 'Get the new row in Sheet2 newrow = Sheet2.Cells(Cells.Rows.Count, 2).End(xlUp).Row + 1 'Load these simple arrays iSource = Array(21, 26, 28, 22, 16, 12, 25, 13, 35) ' Vessel, Bill of Lading Date, Tonnes Loaded, Load Port, Commodity, Inv No, L/C No, Inv Date, Inv Value iTarget = Array(2, 3, 4, 5, 6, 8, 9, 10, 11) 'Ditto 'Loop through the arrays For i = 1 To 9 Sheet2.Cells(newrow, iTarget(i)).Value2 = Sheet1.Cells(iSource(i), SOURCE_COL).Value2 Next i 'Get the other bit of data not in column P Sheet2.Cells(newrow, 7).Value2 = Sheet1.Cells(10, 2).Value2 End Sub
So the array part of this post which is I think is pretty handy is mapping nine bits of information from Sheet1 ‘ISource’ rows to Sheet2 ‘ITarget’ Columns. There needs to be the same number of elements in each array (logically as you are mapping from one bit to another) and note the Option Base 1 declaration allows us the more readily understandable For i = 1 to 9 part of the For Loop. Without that declaration it would simply be For i = 0 to 8 which is still 9 elements so no big deal.
The “newrow” variable is loaded with the last row in Sheet2 plus 1 row for the new line we want to populate.
Now you might be thinking forget the array approach and just put in 9 more statements like the one used for the Customer Name ie Sheet2.Cells(newrow, 7).Value2 = Sheet1.Cells(10, 2).Value2 . True, you could use this ‘long hand approach’. The array approach is simpler to read and maintain within VBA and kind of cool but the ‘long hand’ approach works fine also. If you were using this approach to loop through 1000’s of transactions the array approach I think would be quicker.
That’s it! I think this is fairly common problem to solve out there in any business especially small business. In fact, I should be using this myself for my small consultancy! In reality, I would build in some functionality to safe guard against the button being pressed twice and the data being duplicated before updating.
If you need help with setting up a time saving solution like this please contact me especially if you are in the Gold Coast or Brisbane Qld areas or the Northern NSW region.