autorun macro with vlookup function

[STEEL010]

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

 

[Joe4]

Re: autorun macro with vlooup funtion

Your syntax is not correct.
It should either be:

[COLOR=#333333]If Target.Address = "$B$5" Then[/COLOR]

or

[COLOR=#333333]If Target.Address(0, 0) = "B5" Then[/COLOR]

 

[STEEL010]

Re: autorun macro with vlooup funtion

Thanks for your quick reply, but no still not working

 

[AlphaFrog]

Re: autorun macro with vlooup funtion

Try this...

[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]

Re: autorun macro with vlooup funtion

Sorry but no not working

 

[AlphaFrog]

Re: autorun macro with vlooup funtion

Sorry but no not working

That's hardly descriptive of what happened. Did it return an error, blank value, wrong value?

What range is pt_NL ?

 

[STEEL010]

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]

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]

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]

Re: autorun macro with vlooup funtion

Why don't you just have the VLOOKUP formula in cell B6?