VBA Code VLookup within the Same Workbook - Issues w/Code

[OilEconomist]

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

 

[L. Howard]

Maybe try something like this, where the variable rows on sheet 2, column D is counted for last row in the column as lRow
The formulas are returned to sheet 1, column L, row 2 and down.

Two options for the vlookup formula:
One is the formula will be in the K column cell, and will show the #N/A if no value is found.
Two is the IFERROR and : .Value = .Value where nothing is shown if an error, along with the :Value, and will show only the values of the formula, there will be NO formulas in the K column cells.

For the vlookup formula, I used the sheet 2 columns D:G for the Table_Array, as you are returning the fourth column as the Column Index. There is no need to extend the table_array to column K.

I may have the ranges wrong but should be easy enough to adjust. If problems, post back.

Copy to a standard module and run the code from Sheet 1.

Howard

Option Explicit

Sub my_VLook_Column()

  Dim lRow&
 
  lRow = Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row '/ column D on sheet 2

  With Range("L2").Resize(lRow)

    '.Formula = "=VLOOKUP(M2,Sheet2!$D$1:$G$" & lRow & ",4,0)"

    .Formula = "=IFERROR(VLOOKUP(M2,Sheet2!$D$1:$G$" & lRow & ",4,0),"""")": .Value = .Value

  End With

End Sub

 

[OilEconomist]

Thanks so much Howard (L. Howard) that worked perfect and you even gave me an alternative. Additionally, the way you wrote the code will allow me to learn from it.

 

[OilEconomist]

Actually there seems to be one issue, the 1Row is the number of rows in the Lookup Table in Sheet2. Where I'm returning the values - Sheet 1 has a lot more rows.

I think I need to also declare 2Row& where it is equal to:

2Row = Sheets("Sheet1").Cells(Rows.Count, 13).End(xlUp).Row '/ column AC on sheet 1 where the value is returned.

What is the "&" for in the "Dim 1Row&" where the "1Row" is used, but "1Row&" is not used.

 

[L. Howard]

Okay, look at this code.

lRow is actually the lowercase "L" and "Row", not 1 (one).

I switched to lastRow to be clearer.

The & in the Dim statement of lastRow declares the variable type. It could also be declared as Dim lastRow as Long

Column AC is column number 29. So now the table_array is D1:G? (whatever the last row in column AC is)

Howard

Sub my_VLook_Column_1()

  Dim lastRow&

  lastRow = Sheets("Sheet1").Cells(Rows.Count, 29).End(xlUp).Row '/ column AC on sheet 1

  With Range("L2").Resize(lastRow)

    '.Formula = "=VLOOKUP(M2,Sheet2!$D$1:$G$" & lastRow & ",4,0)"
    .Formula = "=IFERROR(VLOOKUP(M2,Sheet2!$D$1:$G$" & lastRow & ",4,0),"""")": .Value = .Value

  End With

End Sub

 

[L. Howard]

Just to add, the formulas will be returned in L column down as far as the lastRow value equals.

Howard

 

[OilEconomist]

Thanks so much and apologies for the late response. This fixed it.

 

[L. Howard]

Glad to hear, thanks for the feedback.

Howard