“VBA? You wrote VBA code for that?”

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..

• Some great free scripting tools. - 2010
• Deleting files so they can't be recovered. - 2008