Overview of the steps

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

What the Recorder Spit out

Sub Add_VLookup()
'
' Add_VLookup Macro
'

'
    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()
'
' Add_VLookup Macro
'

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
    ActiveWorkbook.Worksheets("SUNDAY").Sort.SortFields.Add2 Key:=Range("F2:F569" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("SUNDAY").Sort
        .SetRange Range("A1:G569")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub