VBA Player Piano: Pt III

After 2019 Tax Season I had created an elaborate macro for general accounting's monthly revenue uploads that worked with multiple tabs containing thousands of rows of data to interact between two sheets in while still taking care of my accounting responsibilities. I had also assisted a colleague in collaboration with a front-end developer in the marketing dept to create a price comparison tool. My own team was getting nervous. "Why is he going to meetings with people in other departments? Why is he ignoring his stated responsibilities?"

Office drama nowhere near this level. Instead of a screenplay, I just wrote more code.

The truth is I was already performing my work and had some down-time since it was off season to help improve some company processes. I was approached by an outside consultant who happened to overhear a conversation involving my "wizardry" as well as by a colleague who knew I was studying programming on my own. I could have remained in my little stated responsibilities bubble, but I knew I couldn't deny an opportunity to take on work that was more in line with what I was trying to get into.

My new mission, imposed on by myself, was to begin automating myself out of a job.

My manager had left 6 months before and I was left in charge with additional spreadsheet management and reporting. For the past few months I had been responsible for preparing our department for the month-end close. The first time I did it was purely based on my manager's notes without any hands-on experience. Luckily my other manager was familiar with the process so she could help advise to a degree but never worked on the details. Eventually I found a way to synchronize my old manager's notes combined with some efficiency tweaks to make the month end close process as smooth as possible, albeit in a manual 2-business day process (it used to take 3 days before).

Now that I had created some complex scripts for other departments in addition to working on mild reporting macros for my department, I felt more confident getting my hands dirty with a bigger endeavor. Since I was familiar with constructing these month-end reports, which in turn have their own templates and formulas, I thought I'd simply be automating the point-and-clicks. And in a sense I was, but there was actually no way to achieve point-and-click scripting in this situation as there were too many moving dynamic parts that had to be captured precisely.

Since the first candidate for macro automation had less moving parts, I decided to work in it fist, as it would later give me the code logic to scale for the more complex one later.

I started by declaring and setting variables for the workbook, worksheets and lastrows of all the columns I needed dynamically worked on. This would allow me to dynamically resize the sheets depending on the data dump for the given week or month. Also crucial was the lastRow variable would help dynamically copy down the formulas which these data dumps fed. And finally these formulas would convert to values once saved as a unique file.

I learned early on in VBA scripting the importance of declaring Workbook, Worksheet and Sheets. Without out this VBA doesn't know where to look for what you're trying to transform and you could easily end up with unintended behavior where the script either errors out or performs a transformation on the wrong sheet in the wrong range.

Dim wb as Workbook
Set wb = ActiveWorkbook

Dim myWorkingSheet1 as Worksheet
Dim myWorkingSheet2 as Worksheet
Dim myWorkingSheet3 as Worksheet
Dim myWorkingSheet4 as Worksheet
Set myWorkingSheet1 as Sheets("Sheet 1")
Set myWorkingSheet2 as Sheets("Sheet 2")
Set myWorkingSheet3 as Sheets("Sheet 3")
Set myWorkingSheet4 as Sheets("Sheet 4")

dim lastRowSheet1 As Long
dim lastRowSheet2 As Long
dim lastRowSheet3 As Long
dim lastRowSheet4 As Long
lastRowSheet1 = myWorkingSheet1.Range("B" & Rows.Count).End(xlUp).Row
lastRowSheet2 = myWorkingSheet2.Range("A" & Rows.Count).End(xlUp).Row - 5
lastRowSheet3 = myWorkingSheet3.Range("A" & Rows.Count).End(xlUp).Row

Once I told VBA where to look for what, it was time to gradually mimic the manipulations I'd do manually.

myWorkingSheet1.Range("A18").EntireRow.Resize(lastRowSheet2 - 2).Insert Shift:=xlDown
myWorkingSheet1.Range("H17:AA17").AutoFill Destination:=Range("H17:AA" & lastRowSheet2 + 15)
myWorkingSheet1.Range("B17:D" & lastRowSheet2 + 16).Value = myWorkingSheet2.Range("A6:C" & lastRowSheet2 + 6).Value
myWorkingSheet1.Range("G17:G" & lastRowSheet2 + 16).Value = myWorkingSheet2.Range("E6:E" & lastRowSheet2 + 5).Value
myWorkingSheet1.Range("S17:Y" & lastRowSheet2 + 16).Value = myWorkingSheet2.Range("F6:L6" & lastRowSheet2 + 5).Value

The above code is to resize sheet 1's (primary report) Range based on sheet 2's (data dump) total number of rows -2 (for headers). It then copies the values from the data dump into the primary report's newly created rows. Transforming the value from one range to another is a more efficient way to "copy and paste" than to perform an actual copy and paste.

Now that the data dump has been migrated accurately into the report template, 2 VLOOKUP formulas are performed based on two pre-defined list ranges in order to tie in the names and categories to the data dump's values (in this case amounts and ID #). The categories will all get a default value of type "Target_Label" if nothing is found in the pre-defned list. Finally we autofill the formulas down and then convert the range to values.

myWorkingSheet1.Range("A17").FormulaR1C1 = "=VLOOKUP(RC[1],lookupList,2,FALSE)"
myWorkingSheet1.Range("A17").AutoFill Destination:=Range("A17:A" & lastRowSheet2 + 16)
myWorkingSheet1.Range("A17:A" & lastRowSheet1).Value = myWorkingSheet1.Range("A17:A" & lastRowSheet1).Value
myWorkingSheet1.Range("F17").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],anotherList,2,FALSE),""Target_Label"")"
myWorkingSheet1.Range("F17").AutoFill Destination:=Range("F17:F" & lastRowSheet2 + 16)
myWorkingSheet1.Range("F17:F" & lastRowSheet1).Value = JHAR.Range("F17:F" & lastRowSheet1).Value

Next we want to filter our types based on a certain one so we can re-class it as the upload won't accept the format of this type. This will only be necessary if the type is present. If not, we want it to continue normally. It took a while (a few months) for a specific error to show up, but once it did, it was a matter of wrapping an exception handler to gracefully finish the filter check if no specifie types were present for that period.

On Error Resume Next
With myWorkingSheet1.Range("A16:G16")
    If workingSheet1.AutoFilterMode Then workingSheet1.AutoFilter.Range.AutoFilter
    .AutoFilter field:=3, Criteria1:="*Trouble_Category_One*"
    .AutoFilter field:=4, Criteria1:="Trouble_Category_Two"
         myWorkingSheet1.Range("E17:E" & lastRowSheet1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "RECLASSED_TROUBLE_TO_GOOD_CATEGORY"
On Error GoTo 0

     myWorkingSheet1.ShowAllData
     myWorkingSheet1.AutoFilterMode = False
End With

This entire macro worked under the assumption that the data dump was properly cleaned and entered in the right places, so when I finally presented it to my teammates I had to walk them through where to paste their report data. This part had to be done manually as only they could log in and generate the reports (which had to be massaged in a different macro).

But other than that the process is much more streamlined and once the data is set, a simple click formats all of this. All that was left was for the accountant to ping the GL and input the updated amounts to enter and finall upload, thus closing the month out.

Since I was very familiar with doing this report manually as well as having gotten the ins and outs of VBA in my previous project, I knew this macro was merely an inevitability. It would get done, it was simply a question of how long could I focus on it to finish it as early as possible.

By this point I was feeling cocky and was ready to take on the next report. While this one was about 50 lines of code, all said and done the next one would amount to 250 rows.