exclaimed a colleague.
“Is that like Visual Basic?”
Well kind of, VBA is Visual Basic for Applications. Which is a scripting language for the Microsoft Office system.
I’d had a problem where an extract of an asset report only had the “Last user” and “Last software scan date” on the first record for each computer listed.
I needed each line to have entries, as I was going to sort the data later.
but I wanted this: 
There was over 4000 records in the extract, so I couldn’t “Fill Down”.
I spent some time looking at Excel spreadsheet functions, particularly the IF function, and kept on running into issues.
So I wrote a VBA Macro.
Dim RowNumber As Integer
Dim ComputerNameColumn As Integer
Dim LastUserColumn As Integer
Dim LastSoftwareScanDateColumn As Integer
ComputerNameColumn = 1
LastUserNameColumn = 2
LastSoftwareScanDateColumn = 4
For RowNumber = 2 To 2324
‘ compare Computer Names, if same the current computer name is same as the last
‘ copy the previous “Last User, Last Software Scan Date” columns.
If Cells(RowNumber, ComputerNameColumn) = Cells(RowNumber – 1, ComputerNameColumn) Then
Cells(RowNumber, LastUserNameColumn) = Cells(RowNumber – 1, LastUserNameColumn)
Cells(RowNumber, LastSoftwareScanDateColumn) = Cells(RowNumber – 1, LastSoftwareScanDateColumn)
‘
End If
Next
Yes, it is simple, and you can see it was written for one time use*. But it worked.
You can download the sample spreadsheet here.
* – for persistent use, you might want to detect things like the ActiveSheet, the number of rows; and whether the “Last user/Last software scan date” cells are actually blank before you overwrite them.
On this day..
Related posts: