Hi all.

I am currently working on a code that will input the vlookup function in specified cells. The code works fine if given a static range in the arguments for vlookup. However, I am attempting to make it dynamic, as I will need to apply this macro across multiple workbooks and the sheet ranges are not constant. However, my attempts have yielded no success.

The code is as follows:

Sub LookingUp(A)
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("MatchingNo").Activate
With ActiveWorkbook.Worksheets("MatchingNo")
Dim LastColumn As Long
Dim LastRow As Long
'Dim SM35Range As Range
Dim LastRow2 As Long
Dim LastColumn2 As Long
LastRow2 = ActiveWorkbook.Worksheets("SM35").UsedRange.Rows.C ount
LastColumn2 = ActiveWorkbook.Worksheets("SM35").UsedRange.Column s..Count
LastColumn = .UsedRange.Columns.Count
LastRow = .UsedRange.Rows.Count
For i = 2 To LastRow
For j = 2 To LastColumn
.Cells(i, j).Formula = Application.WorksheetFunction.VLookup(Cells(i, 1), ActiveWorkbook.Worksheets("SM35").Range(Cells(5, 1), Cells(LastRow2, LastColumn2)), j + 1, False)
Next j
Next i
Columns.AutoFit
ActiveWorkbook.Worksheets("MatchingNo").Range(Cell s(1, 1), Cells(LastRow, LastColumn)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End With

ActiveWorkbook.Worksheets("MatchingNo").Activate
ActiveWorkbook.Worksheets("MatchingNo").Range("A1" ).Select
Application.ScreenUpdating = True
End Sub

Any input is much appreciated.

Thank you and regards.