Variant variable

kamran60

New Member
Joined
Sep 20, 2012
Messages
31
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

GRTorres

Active Member
Joined
Nov 19, 2012
Messages
344
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

New Member
Joined
Sep 20, 2012
Messages
31
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:

S07Visa2.00
S08Visa2.00
S09Visa1.15
S10Visa1.25
S11Visa1.15
S12Visa1.25
20.00MC0.19
21.00MC0.19

<tbody>
</tbody>

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
 
Last edited:

GRTorres

Active Member
Joined
Nov 19, 2012
Messages
344
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

New Member
Joined
Sep 20, 2012
Messages
31
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
 

Forum statistics

Threads
1,143,654
Messages
5,720,109
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top