Is There a VBA Vlookup Article?

[Mike54]

I having a problem with trying to get a Vlookup working in VBA.

In the following code the first one works just fine (all on the same sheet) The second version tries to do exactly the same thing but retrieve data from sheet 2 (data duplicated in identical locations)


Private Sub CommandButton1_Click()
Range("B2") = Application.WorksheetFunction.VLookup(Range("A2"), Range("H1:K20"), 4, False)
End Sub


Private Sub CommandButton2_Click()
Range("Sheet1!B3") = Application.WorksheetFunction.VLookup(Range("sheet2!A3"), Range("H1:K20"), 4, False)
End Sub

I've searched the board for "VBA Vlookup" there are over 300 hits so it seems I'm not alone, I also searched Mr Excel articles normally such a rich resourse, nothing. If anyone can help thank you, or if you know of a good but simple example can you please point me in the right direction.
Many thanks
Mike

 

[stanleydgromjr]

Mike54,

Try:
Calling Worksheet Functions From VBA
http://www.cpearson.com/Excel/CallingWorksheetFunctionsInVBA.aspx


Have a great day,
Stan

 

[Peter_SSs]

Mike

You have just used some incorrect syntax for referring to cells when also trying to specify the sheet. Try this instead for button 2:

<font face=Courier New><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        Sheets("Sheet1").Range("B3").Value = Application.WorksheetFunction. _<br>                VLookup(.Range("A3").Value, .Range("H1:K20"), 4, <SPAN style="color:#00007F">False</SPAN>)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Legacy 68668]

Where is you CommandButton ?

When the code is written in the Sheet module, any cell reference without its parent object(Sheet) will be made to parent object of the module.

Private Sub CommandButton2_Click()
Range("Sheet1!B3") = Application.WorksheetFunction.VLookup(Range("sheet2!A3"), Range("H1:K20"), 4, False)
End Sub

 

[Mike54]

Thank you all very much, after 8 hours and no reply I wasn't sure I'd get any help. They all look very useful solutions & I can't wait to try but I'm on vacation for the next 8 days so will have to try on my return. Thanks again that's really great.
Regards
Mike