Help with vlookup
 

Below is my code. I keep receiving a "Type Mismatch" error '13'. And no
matter what I try I can't get it to work. Please help!


Sub GetAnswer()
' Created by CJ
'Enter an abbreviation and this will tell you which branch it is for.
Dim Num As Variant
Dim Answer As Variant
Dim msg As String
Dim Ans As Variant
Dim Nums As Variant

'Prompt for a value
Do
Num = InputBox("What is the branch number you would like to look up?")

'Exit if cancelled
If Num = "" Then Exit Sub

'Look up the abbreviation and return an answer.
Answer = Application.VLookup(Num, "Branch Numbers", 4, False)


MsgBox Answer

'Show which branch the number goes to
MsgBox Num & " is the PDSN Branch #" & Answer & "."

'Ask if they would like to look up another number
msg = MsgBox("Would you like to look up another branch?", vbYesNo)
Loop While msg = vbYes

End Sub

Help with vlookup
 

Hi,

I assume "Branch Numbers" is a named range? try it this way:

Answer = Application.VLookup(Num, Range("Branch Numbers"), 4, False)


--
Hope that helps.

Vergel Adriano


"csjames" wrote:

Below is my code. I keep receiving a "Type Mismatch" error '13'. And no
matter what I try I can't get it to work. Please help!


Sub GetAnswer()
' Created by CJ
'Enter an abbreviation and this will tell you which branch it is for.
Dim Num As Variant
Dim Answer As Variant
Dim msg As String
Dim Ans As Variant
Dim Nums As Variant

'Prompt for a value
Do
Num = InputBox("What is the branch number you would like to look up?")

'Exit if cancelled
If Num = "" Then Exit Sub

'Look up the abbreviation and return an answer.
Answer = Application.VLookup(Num, "Branch Numbers", 4, False)


MsgBox Answer

'Show which branch the number goes to
MsgBox Num & " is the PDSN Branch #" & Answer & "."

'Ask if they would like to look up another number
msg = MsgBox("Would you like to look up another branch?", vbYesNo)
Loop While msg = vbYes

End Sub

Help with vlookup
 

That works. When I'm testing my vlookup using a message box I get the same
run-time error '13': Type mismatch.

See "MsgBox Answer" in code below

tnx

"Vergel Adriano" wrote:

Hi,

I assume "Branch Numbers" is a named range? try it this way:

Answer = Application.VLookup(Num, Range("Branch Numbers"), 4, False)


--
Hope that helps.

Vergel Adriano


"csjames" wrote:

Below is my code. I keep receiving a "Type Mismatch" error '13'. And no
matter what I try I can't get it to work. Please help!


Sub GetAnswer()
' Created by CJ
'Enter an abbreviation and this will tell you which branch it is for.
Dim Num As Variant
Dim Answer As Variant
Dim msg As String
Dim Ans As Variant
Dim Nums As Variant

'Prompt for a value
Do
Num = InputBox("What is the branch number you would like to look up?")

'Exit if cancelled
If Num = "" Then Exit Sub

'Look up the abbreviation and return an answer.
Answer = Application.VLookup(Num, "Branch Numbers", 4, False)


MsgBox Answer

'Show which branch the number goes to
MsgBox Num & " is the PDSN Branch #" & Answer & "."

'Ask if they would like to look up another number
msg = MsgBox("Would you like to look up another branch?", vbYesNo)
Loop While msg = vbYes

End Sub

Help with vlookup
 

if iserror(answer) then
msgbox "no match"
else
msgbox Answer
end if

Are your branch numbers strings are numbers?

maybe:
Answer = Application.VLookup(clng(Num), Range("Branch Numbers"), 4, False)

to convert the string that's returned with the Inputbox to a number.


csjames wrote:

That works. When I'm testing my vlookup using a message box I get the same
run-time error '13': Type mismatch.

See "MsgBox Answer" in code below

tnx

"Vergel Adriano" wrote:

Hi,

I assume "Branch Numbers" is a named range? try it this way:

Answer = Application.VLookup(Num, Range("Branch Numbers"), 4, False)


--
Hope that helps.

Vergel Adriano


"csjames" wrote:

Below is my code. I keep receiving a "Type Mismatch" error '13'. And no
matter what I try I can't get it to work. Please help!


Sub GetAnswer()
' Created by CJ
'Enter an abbreviation and this will tell you which branch it is for.
Dim Num As Variant
Dim Answer As Variant
Dim msg As String
Dim Ans As Variant
Dim Nums As Variant

'Prompt for a value
Do
Num = InputBox("What is the branch number you would like to look up?")

'Exit if cancelled
If Num = "" Then Exit Sub

'Look up the abbreviation and return an answer.
Answer = Application.VLookup(Num, "Branch Numbers", 4, False)


MsgBox Answer

'Show which branch the number goes to
MsgBox Num & " is the PDSN Branch #" & Answer & "."

'Ask if they would like to look up another number
msg = MsgBox("Would you like to look up another branch?", vbYesNo)
Loop While msg = vbYes

End Sub

--

Dave Peterson

Help with vlookup
 

The problem is that there are matches and the code is acting like they don't
exist. I'm using a number to vlookup from a list of numbers. What format
does the input box use when taking the information to vlookup?

"Dave Peterson" wrote:

if iserror(answer) then
msgbox "no match"
else
msgbox Answer
end if

Are your branch numbers strings are numbers?

maybe:
Answer = Application.VLookup(clng(Num), Range("Branch Numbers"), 4, False)

to convert the string that's returned with the Inputbox to a number.


csjames wrote:

That works. When I'm testing my vlookup using a message box I get the same
run-time error '13': Type mismatch.

See "MsgBox Answer" in code below

tnx

"Vergel Adriano" wrote:

Hi,

I assume "Branch Numbers" is a named range? try it this way:

Answer = Application.VLookup(Num, Range("Branch Numbers"), 4, False)


--
Hope that helps.

Vergel Adriano


"csjames" wrote:

Below is my code. I keep receiving a "Type Mismatch" error '13'. And no
matter what I try I can't get it to work. Please help!


Sub GetAnswer()
' Created by CJ
'Enter an abbreviation and this will tell you which branch it is for.
Dim Num As Variant
Dim Answer As Variant
Dim msg As String
Dim Ans As Variant
Dim Nums As Variant

'Prompt for a value
Do
Num = InputBox("What is the branch number you would like to look up?")

'Exit if cancelled
If Num = "" Then Exit Sub

'Look up the abbreviation and return an answer.
Answer = Application.VLookup(Num, "Branch Numbers", 4, False)


MsgBox Answer

'Show which branch the number goes to
MsgBox Num & " is the PDSN Branch #" & Answer & "."

'Ask if they would like to look up another number
msg = MsgBox("Would you like to look up another branch?", vbYesNo)
Loop While msg = vbYes

End Sub

--

Dave Peterson

Help with vlookup
 

Thank you so much DAVE!!! I used the clng() and it worked perfectly. Again,
thanks so much!!!

"csjames" wrote:

The problem is that there are matches and the code is acting like they don't
exist. I'm using a number to vlookup from a list of numbers. What format
does the input box use when taking the information to vlookup?

"Dave Peterson" wrote:

if iserror(answer) then
msgbox "no match"
else
msgbox Answer
end if

Are your branch numbers strings are numbers?

maybe:
Answer = Application.VLookup(clng(Num), Range("Branch Numbers"), 4, False)

to convert the string that's returned with the Inputbox to a number.


csjames wrote:

That works. When I'm testing my vlookup using a message box I get the same
run-time error '13': Type mismatch.

See "MsgBox Answer" in code below

tnx

"Vergel Adriano" wrote:

Hi,

I assume "Branch Numbers" is a named range? try it this way:

Answer = Application.VLookup(Num, Range("Branch Numbers"), 4, False)


--
Hope that helps.

Vergel Adriano


"csjames" wrote:

Below is my code. I keep receiving a "Type Mismatch" error '13'. And no
matter what I try I can't get it to work. Please help!


Sub GetAnswer()
' Created by CJ
'Enter an abbreviation and this will tell you which branch it is for.
Dim Num As Variant
Dim Answer As Variant
Dim msg As String
Dim Ans As Variant
Dim Nums As Variant

'Prompt for a value
Do
Num = InputBox("What is the branch number you would like to look up?")

'Exit if cancelled
If Num = "" Then Exit Sub

'Look up the abbreviation and return an answer.
Answer = Application.VLookup(Num, "Branch Numbers", 4, False)


MsgBox Answer

'Show which branch the number goes to
MsgBox Num & " is the PDSN Branch #" & Answer & "."

'Ask if they would like to look up another number
msg = MsgBox("Would you like to look up another branch?", vbYesNo)
Loop While msg = vbYes

End Sub

--

Dave Peterson

Help with vlookup
 

Sometimes, I bury the lead!

csjames wrote:

Thank you so much DAVE!!! I used the clng() and it worked perfectly. Again,
thanks so much!!!

"csjames" wrote:

The problem is that there are matches and the code is acting like they don't
exist. I'm using a number to vlookup from a list of numbers. What format
does the input box use when taking the information to vlookup?

"Dave Peterson" wrote:

if iserror(answer) then
msgbox "no match"
else
msgbox Answer
end if

Are your branch numbers strings are numbers?

maybe:
Answer = Application.VLookup(clng(Num), Range("Branch Numbers"), 4, False)

to convert the string that's returned with the Inputbox to a number.


csjames wrote:

That works. When I'm testing my vlookup using a message box I get the same
run-time error '13': Type mismatch.

See "MsgBox Answer" in code below

tnx

"Vergel Adriano" wrote:

Hi,

I assume "Branch Numbers" is a named range? try it this way:

Answer = Application.VLookup(Num, Range("Branch Numbers"), 4, False)


--
Hope that helps.

Vergel Adriano


"csjames" wrote:

Below is my code. I keep receiving a "Type Mismatch" error '13'. And no
matter what I try I can't get it to work. Please help!


Sub GetAnswer()
' Created by CJ
'Enter an abbreviation and this will tell you which branch it is for.
Dim Num As Variant
Dim Answer As Variant
Dim msg As String
Dim Ans As Variant
Dim Nums As Variant

'Prompt for a value
Do
Num = InputBox("What is the branch number you would like to look up?")

'Exit if cancelled
If Num = "" Then Exit Sub

'Look up the abbreviation and return an answer.
Answer = Application.VLookup(Num, "Branch Numbers", 4, False)


MsgBox Answer

'Show which branch the number goes to
MsgBox Num & " is the PDSN Branch #" & Answer & "."

'Ask if they would like to look up another number
msg = MsgBox("Would you like to look up another branch?", vbYesNo)
Loop While msg = vbYes

End Sub

--

Dave Peterson


--

Dave Peterson