Hi All,

I am trying to replicate the functionality of a vlookup in vba without using
loads of if statements.
For instance, imagine I have a list of people and there corrosponding ages
that I want to look up. I have an input box which asks for there name and I
am currently using application.worksheetfunction.vloookup to return their age
to a message box.

How can I avoid having the data typed into a range on the woorksheet without
doing a load of 'if name = john then age =35' type lines?

Any help would be gratefully appreciated.

I would have thought that using that worksheet function in your code would work
ok:

dim res as variant 'could return an error
dim namestr as string
namestr = inputbox(Prompt:="name?")
if trim(namestr) = "" then
exit sub
end if

res = application.vlookup(namestr, worksheets("sheet999").range("a:b"),2,false)
if iserror(res) then
msgbox "not a valid name"
else
msgbox Namestr & " is " & res
end if

(untested, uncompiled. watch for typos.)

pablo bellissimo wrote:

Hi All,

I am trying to replicate the functionality of a vlookup in vba without using
loads of if statements.
For instance, imagine I have a list of people and there corrosponding ages
that I want to look up. I have an input box which asks for there name and I
am currently using application.worksheetfunction.vloookup to return their age
to a message box.

How can I avoid having the data typed into a range on the woorksheet without
doing a load of 'if name = john then age =35' type lines?

Any help would be gratefully appreciated.

--

Dave Peterson

Hi Dave,

Thanks for the pointers on the error handling. That will be very useful!

In this example the name and age thing is just a 'for instance'. I suppose
what I am really trying to do is include the name and age in the code so that
it will work with a completely blank workbook. I believe it is called
hardcoding??

What I am actually trying to do may be slightly longer to explain but here
goes...
Sub pauls_predictive()
Dim connum As String
Dim i As Integer


word1 = InputBox("Enter the first word to test")
word2 = InputBox("Enter the second word to test")

len1 = Len(word1)
totalnumber = ""

For i = 1 To len1
currentletter = Mid(word1, i, 1)
connum = Application.WorksheetFunction.VLookup _
(currentletter, Worksheets("Sheet1").Range("A1:b26"), 2)
w1num = w1num & connum
Next i

For i = 1 To len1
currentletter = Mid(word2, i, 1)
connum = Application.WorksheetFunction.VLookup _
(currentletter, Worksheets("Sheet1").Range("A1:b26"), 2)

w2num = w2num & connum

Next i
If w1num = w2num Then
MsgBox ("Same")
Else
MsgBox ("Different")
End If

End Sub

This works (although I'm sure its not very efficient!) but I would like to
replace the vlookup bit with code that doesn't read from the worksheet.

Hope that makes sense!
I have 2 input boxes where a user enters a word in each. I need to convert
that word to the keys that would have to be pressed on a mobile phone to see
if both words would require the same keystrokes. what I have so far is:



"Dave Peterson" wrote:

I would have thought that using that worksheet function in your code would work
ok:

dim res as variant 'could return an error
dim namestr as string
namestr = inputbox(Prompt:="name?")
if trim(namestr) = "" then
exit sub
end if

res = application.vlookup(namestr, worksheets("sheet999").range("a:b"),2,false)
if iserror(res) then
msgbox "not a valid name"
else
msgbox Namestr & " is " & res
end if

(untested, uncompiled. watch for typos.)

pablo bellissimo wrote:

Hi All,

I am trying to replicate the functionality of a vlookup in vba without using
loads of if statements.
For instance, imagine I have a list of people and there corrosponding ages
that I want to look up. I have an input box which asks for there name and I
am currently using application.worksheetfunction.vloookup to return their age
to a message box.

How can I avoid having the data typed into a range on the woorksheet without
doing a load of 'if name = john then age =35' type lines?

Any help would be gratefully appreciated.

--

Dave Peterson

I like application.vlookup() in case there isn't a match.

And if you're looking for an exact match, I think you want to specify false/0 as
the 4th argument in that =vlookup() formula.

And I think I'd loop through the length of the shortest name--or the length of
each name, just in case they aren't the same number of characters.


pablo bellissimo wrote:

Hi Dave,

Thanks for the pointers on the error handling. That will be very useful!

In this example the name and age thing is just a 'for instance'. I suppose
what I am really trying to do is include the name and age in the code so that
it will work with a completely blank workbook. I believe it is called
hardcoding??

What I am actually trying to do may be slightly longer to explain but here
goes...
Sub pauls_predictive()
Dim connum As String
Dim i As Integer

word1 = InputBox("Enter the first word to test")
word2 = InputBox("Enter the second word to test")

len1 = Len(word1)
totalnumber = ""

For i = 1 To len1
currentletter = Mid(word1, i, 1)
connum = Application.WorksheetFunction.VLookup _
(currentletter, Worksheets("Sheet1").Range("A1:b26"), 2)
w1num = w1num & connum
Next i

For i = 1 To len1
currentletter = Mid(word2, i, 1)
connum = Application.WorksheetFunction.VLookup _
(currentletter, Worksheets("Sheet1").Range("A1:b26"), 2)

w2num = w2num & connum

Next i
If w1num = w2num Then
MsgBox ("Same")
Else
MsgBox ("Different")
End If

End Sub

This works (although I'm sure its not very efficient!) but I would like to
replace the vlookup bit with code that doesn't read from the worksheet.

Hope that makes sense!
I have 2 input boxes where a user enters a word in each. I need to convert
that word to the keys that would have to be pressed on a mobile phone to see
if both words would require the same keystrokes. what I have so far is:

"Dave Peterson" wrote:

I would have thought that using that worksheet function in your code would work
ok:

dim res as variant 'could return an error
dim namestr as string
namestr = inputbox(Prompt:="name?")
if trim(namestr) = "" then
exit sub
end if

res = application.vlookup(namestr, worksheets("sheet999").range("a:b"),2,false)
if iserror(res) then
msgbox "not a valid name"
else
msgbox Namestr & " is " & res
end if

(untested, uncompiled. watch for typos.)

pablo bellissimo wrote:

Hi All,

I am trying to replicate the functionality of a vlookup in vba without using
loads of if statements.
For instance, imagine I have a list of people and there corrosponding ages
that I want to look up. I have an input box which asks for there name and I
am currently using application.worksheetfunction.vloookup to return their age
to a message box.

How can I avoid having the data typed into a range on the woorksheet without
doing a load of 'if name = john then age =35' type lines?

Any help would be gratefully appreciated.

--

Dave Peterson


--

Dave Peterson

connum = Application.WorksheetFunction.VLookup _
(currentletter, Worksheets("Sheet1").Range("A1:b26"), 2)

I have 2 input boxes where a user enters a word in each. I need to
convert
that word to the keys that would have to be pressed on a mobile phone to
see
if both words would require the same keystrokes. what I have so far is:

You should be able to eliminate the spreadsheet interaction completely. Show
us what is in Range("A1:B26").

Rick