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

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  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)

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


End Sub
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

Code:
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

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514

ADVERTISEMENT

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
Code:
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Just to add, the formulas will be returned in L column down as far as the lastRow value equals.

Howard
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks so much and apologies for the late response. This fixed it.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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
Top