We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

VLookup issue

Wilder1626
Wilder1626 asked
on
Medium Priority
286 Views
Last Modified: 2012-05-11
Hello all,

I need your help on a VLookup VBA in my excel spredsheet.

=VLookup(S4,Test2!C:N,12,0)

But i just want the result, not the formula.

I would click on a button and i would have the result..

Can you help me please?

Thanks again.


VLookup.xlsm
Comment
Watch Question

Commented:
It's not clear what you want to do here...can you please explain?
CERTIFIED EXPERT

Author

Commented:
If you look in my excel spreadsheet, i have a VLookup in column T going down.

So if there is a value in column S starting at row 4, i want the macro to put the VLookup has a result only. Not a formula.

Is that possible?
CERTIFIED EXPERT
Top Expert 2008

Commented:
Use this macro:

Private Sub CommandButton2_Click()

    With Me.Range([T4], [S10000].End(xlUp).Offset(0, 1))
        .Formula = "=IF(ISNA(VLOOKUP(S4,Test2!C:N,12,0)),""-"",VLOOKUP(S4,Test2!C:N,12,0))"
        .Value = .Value
    End With

End Sub

Kevin
CERTIFIED EXPERT

Author

Commented:
Hello zorvek

This is weird cause when i use you macro, it works.

But has soon has i change sheet names, it does not work anymore.

Ex:

With Me.Range([T4], [S10000].End(xlUp).Offset(0, 1))
        .Formula = "=IF(ISNA(VLOOKUP(S4,3P carrier list!C:N,12,0)),""-"",VLOOKUP(S4,3P carrier list!C:N,12,0))"
        .Value = .Value
    End With

Open in new window


Test2 = 3P carrier list

Do you know why?
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Thank you so much.

It works
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.