Variant variable

[kamran60]

Greetings All,

I have a problem with a vlookup, that is as follows:

I have a list of strings and numbers that the vlookup refers to(call it lookup_value) and picks out the corresponding value in the coloumn next to the coloumn of strings and numbers.

BUT, the problem is this:

If I do a regular vlookup I get a problem of Error 2042 when ever lookup_value is a number. So I decided to dim lookup_value as Variant, but now my lookup_value is fixed at Empty and does not change to what the user wants to find. and returns Error 13: Type Mismatch.

I am really lost and did a whole lot reading and searching. Any clues? THANK YOU

 

[GRTorres]

Hi Kamran60, could you post a quick two or three line example so we can see what you are trying to lookup?

It could be a formatting issue, where the vlookup is not excepting the variable. Thanks.

 

[kamran60]

Hi Kamran60, could you post a quick two or three line example so we can see what you are trying to lookup?

It could be a formatting issue, where the vlookup is not excepting the variable. Thanks.

This is what my Interchange spreadsheet looks like:

[TABLE="width: 128"]
<tbody>[TR]
[TD]S07[/TD]
[TD]Visa[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]S08[/TD]
[TD]Visa[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]S09[/TD]
[TD]Visa[/TD]
[TD="align: right"]1.15[/TD]
[/TR]
[TR]
[TD]S10[/TD]
[TD]Visa[/TD]
[TD="align: right"]1.25[/TD]
[/TR]
[TR]
[TD]S11[/TD]
[TD]Visa[/TD]
[TD="align: right"]1.15[/TD]
[/TR]
[TR]
[TD]S12[/TD]
[TD]Visa[/TD]
[TD="align: right"]1.25[/TD]
[/TR]
[TR]
[TD]20.00[/TD]
[TD]MC[/TD]
[TD="align: right"]0.19[/TD]
[/TR]
[TR]
[TD]21.00[/TD]
[TD]MC[/TD]
[TD="align: right"]0.19[/TD]
[/TR]
</tbody>[/TABLE]

My code is as follows:

-------------
Dim x as Variant

'User enters value of x from the first coloumn of Interchange
y=Application.Vlookup(x,Worksheets("Interchange").Range("A2:C36"), 3, False)
'y returns only numbers in the third coloumn of Interchange spreadsheet
------------------

If I dont dim x as Variant, it will only work for input values of x that are text, numeric values will result in Error 2042. When I dim x as Variant it does not work at all, and x is Empty regardless user inputs a value of x from the first coloumn. I want it to work for all values of text and numbers. Any help or suggestions would be appreciated. Thank you

 

[GRTorres]

I beleive the problem we are going to run into is when we run the Macro, because you are in a way forced to Dim x as a specfic format.

Though, have you considered making a hidden worksheet (xlVeryHidden) so no one can view and imbed a code within the worksheet somewhere to do a simple vlookup?

Placing the vlookup in the sheet itself will fix the problem in a second.

Or will Visa always be a Letter + # format and MC always be a #? This way prompt the user to designate which category they are. But I will continue to look.

 

[kamran60]

Hey thanks for your response. As far the work sheet is concerned, I wont be able to do it because I am working in a userform with a textbox( which I defined as x) and the user inputs any value of Coloumn A into that textbox. That textbox value will implement certain commands in the userform (ie more calculations) which I may not be able to do using a worksheet. I could be wrong so please correct me. Moreover, just to clarify my problem a bit more, x takes in values such as S07, S08...20.00(first coloumn of Interchange spreadsheet) and Application.Vlookup will output values like 2.00, 1.25, ...0.19(Third coloumn of Interchange Spreadsheet). Therefore I dont understand why you mentioned 'Visa Letter+# format and MC be always #?' ?.The Application.Vlookup does not deal with second coloumn, it only deals with first and third coloumn. As again your help is always appreciated