Vlookup table array equal to last row????

[i_wish_i_knew]

Hi Guys.
Tried so many different fixes for this and i'm just not getting it.
I have 2 sheets in my work book. the first names "MC.1" the second named "SQ01"
I wish to set up a macro (linked to a button) that once pressed enters a vlookup formula in cell L2 in the SQ01 sheet. the vlookup formula needs to find a2 from "SQ01" within the table array a2:j...... in sheet "MC.1" and bring through the result in column H (8)
The number of values in mc.1 varies, at the moment there are 27,000+ rows. so id like a last row kind of table array so that the lookup will work with every update of the sheet with varying row numbers.
Id then also like the VLookup formula to be dragged the whole way down column L in SQ01 (all linked to the same locked table array). the number of rows in SQ01 also varies sp this would also need to reference a last row formula.

hopefully this makes sense.

thanks so much.

 

[dchaney]

Do you really need the Formula for VLookup to be added? I mean instead of adding the Vlookup with VBA you can just have VBA lookup the info.

 

[dchaney]

IF you do not need the formula and you just need the value added to L2 on SQ01 when the button is pressed, then add the following to your button_Click sub

Dim lastRow As LongDim wsSQ As Worksheet, wsMC As Worksheet

Set wsSQ = Sheets("SQ01")
Set wsMC = Sheets("MC.1")

lastRow = wsMC.Range("A" & Rows.Count).End(xlUp).Row
wsSQ.Range("L2") = Application.WorksheetFunction.VLookup(wsSQ.Range("A2"), wsMC.Range("A2:J" & lastRow), 8, False)

This should provide you what you seek

 

[i_wish_i_knew]

Hello, many thanks for your help with this, this works well in cell L2 for the VLookup, however when I extend the macro to copy this the whole way down column L, it simply copies the result in L2, not the VLookup formula, is there a way to extend the formula so it then uses the same range in MC.1, but moves the reference field to L3, then L4 as it moves the formula down the column?

 

[dchaney]

Here you are, I missed in your OP that you needed this to extend the entire column L. Let me know if there are still issues.

Dim lastRow As Long, finalRow As Long
Dim wsSQ As Worksheet, wsMC As Worksheet

Set wsSQ = Sheets("SQ01")
Set wsMC = Sheets("MC.1")

lastRow = wsMC.Range("A" & Rows.Count).End(xlUp).Row
finalRow = wsSQ.Range("A" & Rows.Count).End(xlUp).Row

wsSQ.Range("L2:L" & finalRow) = Application.WorksheetFunction.VLookup(wsSQ.Range("A2:A" & finalRow), wsMC.Range("A2:J" & lastRow), 8, False)