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

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" wrote in
message ...

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

--
bisjo
-----------------------------------------------------------------------
bisjom's Profile: http://www.excelforum.com/member.php...fo&userid=3120
View this thread: http://www.excelforum.com/showthread.php?threadid=50879

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" wrote in
message ...

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


--
bisjom
------------------------------------------------------------------------
bisjom's Profile: http://www.excelforum.com/member.php...o&userid=31206
View this thread: http://www.excelforum.com/showthread...hreadid=508799

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" wrote in
message ...

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


--
bisjom
------------------------------------------------------------------------
bisjom's Profile: http://www.excelforum.com/member.php...o&userid=31206
View this thread: http://www.excelforum.com/showthread...hreadid=508799