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

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
371
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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

Active Member
Joined
Dec 26, 2016
Messages
371
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

Active Member
Joined
Dec 26, 2016
Messages
371
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

Active Member
Joined
Dec 26, 2016
Messages
371
Office Version
  1. 2019
Platform
  1. Windows
Thanks so much and apologies for the late response. This fixed it.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,857
Messages
5,834,008
Members
430,251
Latest member
fscrn_ffg

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