Info about "WorksheetFunction.VLookup"

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
669
Office Version
  1. 2007
Platform
  1. Windows
Hi all,
how can I to transform:

MioValore =Application.WorksheetFunction.VLookup(Worksheets("Report").Range("A2"),Worksheets("Cee").Range("A:J"), 6, False

with:

NrFT = InputBox("Input N° Document", "Hi")

i.e.:

MioValore = Application.WorksheetFunction.VLookup(NrFT,Worksheets("Cee").Range("A:J"), 6, False)


Tia.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hello Maurizio,

Does that not work for you? Can you explain what does/does not work here?
 

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
669
Office Version
  1. 2007
Platform
  1. Windows
Hi firefytr,

with Excel 2000 on Win98Se I get:

error run-time 1004: impossible to find property of Vlookup on class WorksheetFunction.


Bye.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
That means the value was not found in the left-most column of your range given.
 

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
669
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Sorry, but is not so!

"That means the value was not found in the left-most column of your range given".

solution:

MioValore = Application.WorksheetFunction.VLookup(NrFT * 1 ,Worksheets("Cee").Range("A:J"), 6, False)
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
You were trying to locate a String in a numeric range - coercing it via * 1 as you've found, will fix that.

Also see the VBA Cdbl() function.
 

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
669
Office Version
  1. 2007
Platform
  1. Windows
Ok just_jon,
so this solution is best:

Code:
NrFT = Application.InputBox("Input N° Doc", "Ciao")
    
    If IsNumeric(NrFT) Then
        NrFT = CDbl(NrFT)
        'MsgBox (" i.e. Nr")
    Else
        'MsgBox (" i.e. txt")
    End If

MsgBox Application.VLookup(NrFT, Worksheets("Cee").Range("A:B"), 2, False)
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Maurizio said:
Ok just_jon,
so this solution is best:

NrFT = Application.InputBox("Input N° Doc", "Ciao")

If IsNumeric(NrFT) Then
NrFT = CDbl(NrFT)
'MsgBox (" i.e. Nr")
Else
'MsgBox (" i.e. txt")
End If

MsgBox Application.VLookup(NrFT, Worksheets("Cee").Range("A:B"), 2, False)

I would do:

Dim NfFT as Long
NrFT = Application.InputBox("Input N° Doc", "Ciao")
MsgBox Application.VLookup(NrFT, Worksheets("Cee").Range("A:B"), 2, False)


as long as you know the intended input needs to be found in a numeric range A
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top