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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

RudiS

Active Member
Joined
May 7, 2015
Messages
349
Try:
Code:
Range("M7").Value = Application.WorksheetFunction.VLookup(Range("F7").Value, Sheets("Data").Range("$B$2:$V$65536"), 21, False)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,518
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!
 

VeryForgetful

Board Regular
Joined
Mar 1, 2015
Messages
242
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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,786
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,786
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

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
Top