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


Active Member
Dec 26, 2016
Office Version
  1. 2019
  1. 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)

If Err.Number = 1004 Then
result = “TBD”
End If

End Sub

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.


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
Upvote 0
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.
Upvote 0
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.
Upvote 0
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)

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
Upvote 0
Just to add, the formulas will be returned in L column down as far as the lastRow value equals.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back