Pull data based from another sheet based on a cell value-CodePudding

I'm very new with VBA. I have a worksheet "Test" and another sheet "Source" (where data/values will come from). I need to pull up the values for all the categories from "Source" sheet based on the value on sheet "Test" cell B2

Test sheet:

Source sheet

What I need to happen is depending on the value I put on cell B2 on Test sheet, when I click Retrieve button, other values will be filled out.

Sub Retrieve()

Sheets("Test").Range("B1").Formula = "=VLOOKUP(B2,Source!$A:$E,1,0)"
Sheets("Test").Range("B3").Formula = "=VLOOKUP(B13,Source!$A:$I,3,0)"
Sheets("Test").Range("B4").Formula = "=VLOOKUP(B13,Source!$A:$I,4,0)"
Sheets("Test").Range("B5").Formula = "=VLOOKUP(B13,Source!$A:$I,5,0)"

End Sub

How do I place an Iferror in the above Vlookup formula?

CodePudding user response：

Is this what you are trying?

With Sheets("Test")
    .Range("B1").Formula = "=IFERROR(INDEX(Source!$A:$A,MATCH($B$2,Source!$B:$B,0),1),"""")"
    .Range("B3").Formula = "=IFERROR(VLOOKUP($B$2,Source!$B:$E,2,0),"""")"
    .Range("B4").Formula = "=IFERROR(VLOOKUP($B$2,Source!$B:$E,3,0),"""")"
    .Range("B5").Formula = "=IFERROR(VLOOKUP($B$2,Source!$B:$E,4,0),"""")"
End With