macros for vlookup in dynamic range with two worksheets

[Luke M]

Hi trividha,

You'll get more responses if you start a new thread rather than tacking onto an older one (especially one from 2015).

 

[Ali MC]

How's this? Change the sheet names as appropriate.

Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet

'What are the names of our worksheets?
Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("Sheet2")

'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
    'Determine last row in col P
    OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
    .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
End Sub

many thanks this was very helpful but , I want this macro ignore (NA# ) and replace it with 0.

thanks in advance!

 

[vletm]

Ali MC

VLOOKUP function - Microsoft Support

Learn how to use function VLOOKUP in Excel to find data in a table or range by row. Our step-by-step guide makes vlookup in excel easy and efficient.

support.office.com

How to correct a #N/A error in the VLOOKUP function - Microsoft Support

Correct the #N/A errorin the VLOOKUP function

support.office.com

 

[Luke M]

many thanks this was very helpful but , I want this macro ignore (NA# ) and replace it with 0.

thanks in advance!

Change the one line of code from this

"=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"

to this

"=IFERROR(VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0), 0)"