OilEconomist
Board Regular
 Joined
 Dec 26, 2016
 Messages
 226
 Office Version

 2019
 Platform

 Windows
Hello and thanks in advance for your help. I will let you know what the final code that works.
I know I can do this with a simple VLookup in a column, but this sheet is downloaded each month and I'm trying to automate the process.
Objective: Write a VBA Code for unknown number of rows (i.e. changes each month) in one sheet (Sheet1) with another sheet (Sheet2).
Formula if using simple VLookup typed in the cell: VLookup('Sheet1'!M2,'Sheet2'!D1:K1708,4,false). Please note I have the range for K1708, but I want that to be dynamic in the case of extra rows being added.
Sub Test()
Dim ws As Worksheet
Dim LastRow As Long
Dim TargetRange As Range
On Error GoTo MyErrorHandler:
Set ws = Sheets("Sheet2")
LastRow = ws.Cells(Rows.Count, "K").End(xlUp).Row
Set TargetRange = ws.Range("D1:K" & LastRow)
result = Application.WorksheetFunction.VLookup(Sheets("Sheet1").Range("M2"), TargetRange, 4, False)
MyErrorHandler:
If Err.Number = 1004 Then
result = “TBD”
End If
End Sub
I know I can do this with a simple VLookup in a column, but this sheet is downloaded each month and I'm trying to automate the process.
Objective: Write a VBA Code for unknown number of rows (i.e. changes each month) in one sheet (Sheet1) with another sheet (Sheet2).
Formula if using simple VLookup typed in the cell: VLookup('Sheet1'!M2,'Sheet2'!D1:K1708,4,false). Please note I have the range for K1708, but I want that to be dynamic in the case of extra rows being added.
Sub Test()
Dim ws As Worksheet
Dim LastRow As Long
Dim TargetRange As Range
On Error GoTo MyErrorHandler:
Set ws = Sheets("Sheet2")
LastRow = ws.Cells(Rows.Count, "K").End(xlUp).Row
Set TargetRange = ws.Range("D1:K" & LastRow)
result = Application.WorksheetFunction.VLookup(Sheets("Sheet1").Range("M2"), TargetRange, 4, False)
MyErrorHandler:
If Err.Number = 1004 Then
result = “TBD”
End If
End Sub