• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

VLookup issue

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
0
Wilder1626
Asked:
Wilder1626
  • 3
  • 2
1 Solution
 
jppintoCommented:
It's not clear what you want to do here...can you please explain?
0
 
Wilder1626Author 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?
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Wilder1626Author 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?
0
 
zorvek (Kevin Jones)ConsultantCommented:
Change:

        .Formula = "=IF(ISNA(VLOOKUP(S4,3P carrier list!C:N,12,0)),""-"",VLOOKUP(S4,3P carrier list!C:N,12,0))"

to:

        .Formula = "=IF(ISNA(VLOOKUP(S4,'3P carrier list'!C:N,12,0)),""-"",VLOOKUP(S4,'3P carrier list'!C:N,12,0))"

Kevin
0
 
Wilder1626Author Commented:
Thank you so much.

It works
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now