Can anybody tell me what is wrong with this syntax?

bk

Active Member
Joined
Jun 2, 2002
Messages
387
Code:
ActiveSheet.Range("a1").Formula = "=MATCH("" & UserForm1.ComboBox1.Value & "",database!A3:A9),0)+2"

'database' is a sheet with the values that fill the ComboBox; these values are text


It looks like it should work to me. I've tried assigning the UF.CBx.Value to a variable first and still no dice.

Thanks for any help.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I believe the problem is with your quotes, VBA cannot figure which ones are used to denote a text entry, and which are literal quote entries. Try using Chr(34) to return the literal quote entries, i.e.

Code:
ActiveSheet.Range("a1").Formula = "=MATCH(" & Chr(34) & Chr(34) & UserForm1.ComboBox1.Value & Chr(34) & Chr(34) & ",database!A3:A9),0)+2"

P.S. I am not sure why you have "" at the beginning and end, but I assume that it is there for a good reason.
 
Upvote 0
No, that didn't work.

Thanks, though.

The quotes are so that the loaded formula will have quotes on either side of UF.ComboBox1's value. This is necessary for the MATCH function.

I don't know if it's textbook or not, but whenever I've been loading a formula into a cell via VBA and needed some quotes to actually be a part of the formula inside the cell, I've always typed twice as many quote signs and it's always worked. I'm self-taught; so, that may be a bad idea, but it's always worked.

This code is inside a Module and I thought it might not like using the UserForm "stuff" here, but I've experimented with placing the UF.CB.Value in a cell and it worked and I experimented with putting this formula as a part of the UF's code module. All w/ no luck.

I load VLOOKUPs like this all the time. The only thing that is new (other than this is a MATCH being loaded via code instead of a VL) is that I'm placing the UF stuff in the formula. I don't recall if I've ever done that w/ a VL or not. But, like I said, none of the work-arounds I've tried seem to work either.

I'm open to any other ideas.
 
Upvote 0
In that case, try:
Code:
ActiveSheet.Range("a1").Formula = "=MATCH(" & Chr(34) & UserForm1.ComboBox1.Value & Chr(34) & ",database!A3:A9),0)+2"
 
Upvote 0
Actually,

what you posted this second time is what I did try. By you asking about my quotation marks, I figured that's what you were trying to achieve with the doubling of chr(34) and left them out when I did my try.

Thanks again.

Still needing help.
 
Upvote 0
What error do you get? Where is this code located? What code are you running? How?
bk said:
Code:
ActiveSheet.Range("a1").Formula = "=MATCH("" & UserForm1.ComboBox1.Value & "",database!A3:A9),0)+2"

'database' is a sheet with the values that fill the ComboBox; these values are text


It looks like it should work to me. I've tried assigning the UF.CBx.Value to a variable first and still no dice.

Thanks for any help.
 
Upvote 0
The code is in a Module (but I've tried it as a part of the UserForm's code as well).

I was getting an Application.... error. I don't remember exactly what it said, but if I clicked help it brought up the help topic about "Raise" and similar errors (that may not be enough detail).

Since yesterday, I've changed the syntax to:
Code:
ActiveSheet.Range("a1").Formula = "=MATCH(" & UserForm1.ComboBox1.Value & ",'database'!A3:A9,0)+2"

and I'm now getting a "Run-time Error 13: Type mismatch".

So, I've at least changed things.

I don't comprehend what you're asking when you ask:
What code are you running? How?
The event triggering the code is the clicking of a Command Button on the UserForm. The UserForm is not hidden until after the above code has executed (I've reversed the order, though, with same error).

Thanks again for any help.
 
Upvote 0
FIXED!

Thanks for all the suggestions and help.

This works:
Code:
ActiveSheet.Range("a1").Formula = "=MATCH(""" & UserForm1.ComboBox1.Value & """,database!A3:A9,0)+2"

I thought I tried this yesterday and I'll have to absorb why this particular combination works. But it does work. Thanks again; I add here in case it helps somebody else in the future.
 
Upvote 0

Forum statistics

Threads
1,217,253
Messages
6,135,487
Members
449,942
Latest member
Gitad

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
Back
Top