vba vlookup function error

VeryForgetful

Board Regular
Joined
Mar 1, 2015
Messages
242
Hi,

I'm getting a runtime 424 object required error with the code below. I was originally getting a 1004 unable to get the worksheet property of the vlookup class.

Code:
Private Sub Worksheet_Activate()

Range("M7").Value = Application.WorksheetFunction.VLookup(F7.Value,Sheets("Data").Range("$B$2:$V$65536"), 21, False)

Range("M7").Copy Range("M8:M" & Cells(Rows.Count, 4).End(xlUp).Row + 0)

End Sub

Any idea what is wrong with this code?

Thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try:
Code:
Range("M7").Value = Application.WorksheetFunction.VLookup(Range("F7").Value, Sheets("Data").Range("$B$2:$V$65536"), 21, False)
 
Upvote 0
Also, to trap errors, try something like this...

Code:
[COLOR=darkblue]Dim[/COLOR] vMatchVal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]

vMatchVal = Application.VLookup(Range("F7").Value, Sheets("Data").Range("$B$2:$V$65536"), 21, [COLOR=darkblue]False[/COLOR])

[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsError(vMatchVal) [COLOR=darkblue]Then[/COLOR]
    Range("M7").Value = vMatchVal
    Range("M7").Copy Range("M8:M" & Cells(Rows.Count, 4).End(xlUp).Row + 0)
[COLOR=darkblue]Else[/COLOR]
    [COLOR=green]'Do something else here[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

Hope this helps!
 
Upvote 0
Thanks both,

I still have an issue with this.

It is performing the vlookup as expected but instead of keeping the formula, the worksheet is displaying just the value of the vlookup.

Also, it is pasting the top vlookup value to each of the other cells rather than doing an independent on each of the range below.

Basically, what I am trying to achieve is for cell M7 to perform the lookup then copy down to as many cells as required (however far down column F goes).

So, for example:

Range M7 would have the formula =VLOOKUP(F7,Data!$B$2:$V$65536,21,0)
Range M8 would have the formula =VLOOKUP(F8,Data!$B$2:$V$65536,21,0)

I've tried replacing .value with .formula but that didn't work.

Sorry, didn't explain it very well to start with.

Thanks
 
Upvote 0
Maybe ...

Code:
Private Sub Worksheet_Activate()
  With Range("M7", Cells(Cells(Rows.Count, "F").End(xlUp).Row, "M"))
    .Formula = "=VLOOKUP(F7, Data!$B$2:$V$65536, 21, False)"
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top