Overview of the steps
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.
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
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