Can anybody tell me what is wrong with this syntax?

[bk]

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.

 

[Joe4]

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.

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.

 

[bk]

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.

 

[Joe4]

In that case, try:

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

 

[bk]

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.

 

[tusharm]

What error do you get? Where is this code located? What code are you running? How?

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.

 

[bk]

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:

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.

 

[bk]

FIXED!

Thanks for all the suggestions and help.

This works:

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.