Type mismatch in look up function

[bisjom]

Hi Guys,

I was trying to get some values from excel sheets to a form in excel
and it shows error “ Type mismatch”. The value in the A coulmn of the
sheet “Answer” is a number and that of B is a text. I have to get that
text value in the text box of my form. The code is as follows:
UserForm1.txtIncome.Value = Application.Lookup(Sheets("Master
I&P").Range(Q207 & j).Value, Sheets("Answer").Range("A2:A334"),
Sheets("Answer").Range("B2:B334"))

Do you know Y this error comes??

Please help

 

[Tom Ogilvy]

Try it like this

Dim res as Variant
res = Application.Lookup(Sheets("MasterI&P").Range( _
Q207 & j).Value, Sheets("Answer").Range("A2:A334"), _
Sheets("Answer").Range("B2:B334"))
if iserror(res) then
msgbox "Lookup value not found"
Else
Userform1.txtIncome.Value = res
End if

The problem is more than likely that your lookup function is not successful.

 

[bisjom]

Hi tom..
I tried that .. it shows subscription out of range.. y...

i am confused..

 

[Tom Ogilvy]

That means when I copied your wordwrapped line of code, I was not able to
discern where you might of had spaces in the names of the
workbook/worksheet. As such, I may have misnamed them. Check the naming
of the workbook/worksheets in the code and correct it.

 

[bisjom]

hi tom,
That was true.. now its working and shows the lookup value not found..
that means it cant get the value.. its there in the answer sheet and i
dont know y this shows the error..
is it neccessary that the value that is to be searched and the value in
the sheet should be of same format..
coz.. the value in the Answer sheet is general and the in the other
sheet is number...

 

[Tom Ogilvy]

They need to be of the same type. I suspect that one is stored as a number
and one is stored as a string.

for example if I search for the Number 123 and the search range contains the
string "123" (no double quotes, just stored as a string), then the match
won't be made (same for the reverse situation).

You could try doing it twice:



Dim res as Variant
res = Application.Lookup(clng(Sheets("Master I&P").Range( _
Q207 & j).Value), Sheets("Answer").Range("A2:A334"), _
Sheets("Answer").Range("B2:B334"))
if iserror(res) then
res = Application.Lookup(cStr(Sheets("Master I&P").Range( _
Q207 & j).Value), Sheets("Answer").Range("A2:A334"), _
Sheets("Answer").Range("B2:B334"))
end if
if iserror(res) then
msgbox "Lookup value not found"
Else
Userform1.txtIncome.Value = res
End if

If the number isn't an integer, then change clng to cdbl

 

[bisjom]

hi tom..
its working fine.. thanks very much