Help inserting formula in a Macro

[RoccoM]

Hi:
I have an existing formula that works well for me. I would like to use it in a macro.
So I would like to copy the formula in cell Q2.

First here is the formula
=IF(F2="y",(IF(((ISNUMBER(SEARCH("blade",E2))*AND(B2="Server"))),"Blade Server","Card")),(IF(((ISNUMBER(SEARCH("blade",E2))*AND(B2="Server"))),"Blade Enclosure",PROPER(B2))))

Here is what I have in terms of the code
Range("Q2").Formula = "=IF(F2="y",(IF(((ISNUMBER(SEARCH("blade",E2))*AND(B2="Server"))),"Blade Server","Card")),(IF(((ISNUMBER(SEARCH("blade",E2))*AND(B2="Server"))),"Blade Enclosure",PROPER(B2))))"

I get the following error, even before I try to run my macro.
Compile error:
Expected: end of statement

Range("Q2").Formula = "=IF(F2="y",(IF(((ISNUMBER(SEARCH("blade",E2))*AND(B2="Server"))),"Blade Server","Card")),(IF(((ISNUMBER(SEARCH("blade",E2))*AND(B2="Server"))),"Blade Enclosure",PROPER(B2))))"

How can I copy the above formula into cell Q2?
Thank you,
Rocco

 

[Joe4]

This issue is that double-quotes are treated as Text Qualifiers when building your formulas in VBA, so it is causing issues.

The easiest thing to do is the following:
1. Turn on the Macro Recorder (from the Macro menu)
2. Go to cell Q2
3. Manually enter your formula in that cell:

[COLOR=#333333]=IF(F2="y",(IF(((ISNUMBER(SEARCH("blade",E2))*AND(B2="Server"))),"Blade Server","Card")),(IF(((ISNUMBER(SEARCH("blade",E2))*AND(B2="Server"))),"Blade Enclosure",PROPER(B2))))

4. Stop the Macro Recorder
5. View the VBA code you just recorded, and copy the formula portion to the spot you want it in your VBA code. This will give you the formula exactly the way that VBA needs it.

So, essentially, you are just letting the Macro Recorder do the work of writing the formula in VBA format for you!
[/COLOR]

 

[RoccoM]

PERFECT! Worked like a charm!
I'm starting to get it
Thank you for the very quick reply.
Rocco

 

[Joe4]

You are welcome.
Glad I was able to help!

Yes, the Macro Recorder is a great little tool to get snippets of code. Just record yourself performing the step manually, and you will have VBA code to do it. Even us "experts" use it!