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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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
20,230
Office Version
  1. 365
Platform
  1. Windows
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,833
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,833
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,294
Messages
5,836,464
Members
430,432
Latest member
Spike47

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