Uncategorized

Updated Nov 7th, 2021

Overview of the steps

• Select the Cell
• Add Vlookup Formula with Dyamic Row Number
• Autofill Based on Dynamic Row Number
• Optional Select the CPP
• Autofill Based on Dynamic Row Number
• Format for Number with one decimal place

What the Recorder Spit out

``````Sub Add_VLookup()
'
'

'
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5], ALL!R1C1:R499C12, 12, FALSE)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F569")
Range("F2:F569").Select
Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-1]"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G569")
Range("G2:G569").Select
Columns("G:G").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Range("A1").Select
ActiveWorkbook.Save
End Sub
``````

Not wanting to add the salary tab since it’s easy to select the necessary “salary” tab we want this to run on. This way we can have multiple tabs for each slate and do not need to rename tab.

Interesting there is no \$ in the VLOOKUP formula to keep the reference static. Still works as intended.

Adding dynamic rows should be easy based on what we just did with the “ALL” tab macro. The dynamic row in the formula may be trickier although as a quick workaround I could easily just use a much larger number like 2000.

It’s obvious the formula uses relative values instead of cell-specific values like “c2:f2”

Converting the row to a number looks very simple.

## Final

``````Sub Add_VLookup()
'
'

Dim LastPlayerRow As Long

LastPlayerRow = Range("A" & Rows.Count).End(xlUp).Row

'
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5], ALL!R1C1:R999C12, 12, FALSE)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & LastPlayerRow)
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/RC[-1]"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & LastPlayerRow)
Columns("G:G").Select
Selection.NumberFormat = "0.0"
Range("A1").Select
ActiveWorkbook.Save
End Sub``````

## Need to Finish With a Sort

Here is the original from the recording that needs to be tweaked and added.

``````Sub Sort_After_VLookup()
'
' Sort_After_VLookup Macro
'

'
Columns("A:G").Select
ActiveWorkbook.Worksheets("SUNDAY").Sort.SortFields.Clear