Thread: Type mismatch in look up function

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

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.

--
Regards,
Tom Ogilvy


"bisjom" <bisjom.22s6dz_1139184001.6569@excelforum-nospam.com> wrote in
message news:bisjom.22s6dz_1139184001.6569@excelforum-nospam.com...
>
> 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
>
>
> --
> bisjom
> ------------------------------------------------------------------------
> bisjom's Profile:
http://www.excelforum.com/member.php...o&userid=31206
> View this thread: http://www.excelforum.com/showthread...hreadid=508799
>

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

i am confused...

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.

--
Regards,
Tom Ogilvy

"bisjom" <bisjom.22s88m_1139186401.3341@excelforum-nospam.com> wrote in
message news:bisjom.22s88m_1139186401.3341@excelforum-nospam.com...
>
> Hi tom..
> I tried that .. it shows subscription out of range.. y...
>
> i am confused...
>
>
> --
> bisjom
> ------------------------------------------------------------------------
> bisjom's Profile:
http://www.excelforum.com/member.php...o&userid=31206
> View this thread: http://www.excelforum.com/showthread...hreadid=508799
>

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...

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

--
Regards,
Tom Ogilvy


"bisjom" <bisjom.22sf6m_1139195402.3893@excelforum-nospam.com> wrote in
message news:bisjom.22sf6m_1139195402.3893@excelforum-nospam.com...
>
> 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...
>
>
> --
> bisjom
> ------------------------------------------------------------------------
> bisjom's Profile:
http://www.excelforum.com/member.php...o&userid=31206
> View this thread: http://www.excelforum.com/showthread...hreadid=508799
>

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