autorun macro with vlookup function

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
76
Hi There,

I got a little held up on my work to autorun an vlookup function in vba.
I want to lookup a value that I created and auto populate in a cell
this is the code but it will not work, please help!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "$B$5" Then
Call lookup
End If
End Sub

Sub lookup()
On Error Resume Next
Range("B6").Value = "=VLookup(B5,pt_NL, 7, 0)"
Range("B6") = Range("B6")
End Sub

Greetings Steel010
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,755
Office Version
  1. 365
Platform
  1. Windows
Re: autorun macro with vlooup funtion

Your syntax is not correct.
It should either be:
Code:
[COLOR=#333333]If Target.Address = "$B$5" Then[/COLOR]
or
Code:
[COLOR=#333333]If Target.Address(0, 0) = "B5" Then[/COLOR]
 
Last edited:

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
76
Re: autorun macro with vlooup funtion

Thanks for your quick reply, but no still not working :(
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
Re: autorun macro with vlooup funtion

Try this...
Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]If[/color] Target.Address(0, 0) = "B5" [color=darkblue]Then[/color]
        [color=darkblue]If[/color] Target.Value <> "" [color=darkblue]Then[/color]
            Range("B6").Value = Evaluate("=VLookup(B5,pt_NL, 7, 0)")
        [color=darkblue]Else[/color]
            Range("B6").ClearContents
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
 

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
76

ADVERTISEMENT

Re: autorun macro with vlooup funtion

Sorry but no not working :(
 

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
76

ADVERTISEMENT

Re: autorun macro with vlooup funtion

pt_NL is table name in an other sheet and created in name manager.
first no error but if I run it with the play (run) button I get a search field with macro's

sorry I can not at a picture or image
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
Re: autorun macro with vlooup funtion

This is an event macro that automatically runs when you make a change to cell B5. It is not a macro that can be run by hitting the play button.

The macro must be installed in the worksheet's code module.

  • Right-click on the sheet tab that has the B5 cell
  • Select View Code from the pop-up context menu
  • Paste the the code in the worksheet's code module

After that, make a change to cell B5.
 

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
76
Re: autorun macro with vlooup funtion

yep correct its on that worksheet, but what I discovered now is that cell B5 is filled in by a inter active combobox.
when you enter a name then it will populate it in B5, now the thing is when I hit the enter button in B5 then cell B6 will be filled with info of my lookup function. How can I do this different then what I'm doing now? is there a way when B5 is filled with info of a combobox that is populate automatically?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
Re: autorun macro with vlooup funtion

Why don't you just have the VLOOKUP formula in cell B6?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,313
Members
425,272
Latest member
Umba

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