• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macros for vlookup in dynamic range with two worksheets

Luke M

Excel Ninja
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

New Member
How's this? Change the sheet names as appropriate.
Code:
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!
 

Luke M

Excel Ninja
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
Code:
"=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
to this
Code:
"=IFERROR(VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0), 0)"
 
Top