How do I combine both SUBs into a single SUB and create a dynamic subtraction formula of column J - column K

[Justin8506]

How do I

1. combine both SUBs into a single SUB
2. create a dynamic subtraction formula of column J - column K

Sub DynamicLookup()
Dim wb As Workbook
Workbooks.Open ("V:\Mar22\Top Segments & Top All_Mar22.xlsx")

Set wb = ActiveWorkbook
ThisWorkbook.Activate
Dim i As Long
For i = 2 To 22

Range("K" & i).Value = WorksheetFunction.Vlookup(Range("F" & i).Value, wb.Sheets("TopSegments").Range("E:I"), 5, 0)
Next i

End Sub

Sub InsertColumn()

Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long

Set ws = Sheet3

With ws
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Cells(1, LastCol + 1).Value = "Variance"

End With

End Sub

Apologies for the redacted info due to confidentiality.

 

[Jamie McMillan]

View attachment 61987


How do I

1. combine both SUBs into a single SUB
2. create a dynamic subtraction formula of column J - column K

Sub DynamicLookup()
Dim wb As Workbook
Workbooks.Open ("V:\Mar22\Top Segments & Top All_Mar22.xlsx")

Set wb = ActiveWorkbook
ThisWorkbook.Activate
Dim i As Long
For i = 2 To 22

Range("K" & i).Value = WorksheetFunction.Vlookup(Range("F" & i).Value, wb.Sheets("TopSegments").Range("E:I"), 5, 0)
Next i

End Sub

Sub InsertColumn()

Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long

Set ws = Sheet3

With ws
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Cells(1, LastCol + 1).Value = "Variance"

End With

End Sub

Apologies for the redacted info due to confidentiality.

Hello,

Combine the Subs simply

Sub Combine
Call DynamicLookup
Call InsertColumn()
End Sub

Jamie

 

[Justin8506]

Hello,

Combine the Subs simply

Sub Combine
Call DynamicLookup
Call InsertColumn()
End Sub

Jamie

Thanks Jamie!