Convert formula to VBA code [SOLVED]

[ssuresh98]

Hello Gurus,

I am new to excel VBA. I am not familiar with variables and loop, hence trying to use activecell.formula. I know this is not the efficient way to do it, but as I am new and just learning found this easy to do.


The following formula works when manually typing in a cell.


Formula:

="=CORREL(L"&ROW()&":BG"&ROW()&",'Master SNP Database'!G" &MATCH(C2, 'Master SNP Database'!C:C,0)& ":BB" &MATCH(C2,'Master SNP Database'!C:C,0)&")"


Desired Result:

=CORREL(L2:BG2,'Master SNP Database'!G530:BB530)


But the same formula when entered in VBA gives compile error: Syntax error. Can someone point me how to overcome the error.


VBA code:

Sub test()


ActiveCell.FormulaR1C1 = "=CORREL(L"&ROW()&":BG"&ROW()&",'Master SNP Database'!G" &MATCH(C2, 'Master SNP Database'!C:C,0)& ":BB" &MATCH(C2,'Master SNP Database'!C:C,0)&")"

End Sub


My excel VBA skills are basic but if given a direction I can work my way up.

TIA.

 

[SirJB7]

Hi, ssuresh98!

Tried double quoting each internal quote in the formula? Then VBA would take the pair as a unique quote and the formula will be properly set.

Regards!

 

[ssuresh98]

Hi SirJB7,

Thanks for your suggestion.

I tried to convert each of the internal quotes into double quote but the code still gives an error.

 

[SirJB7]

Hi, ssuresh98!

Check this:

-----

ActiveCell.Formula = "=CORREL(L""&ROW()&"":BG""&ROW()&"",'Master SNP Database'!G"" &MATCH(C2, 'Master SNP Database'!C:C,0)& ":BB"" &MATCH(C2,'Master SNP Database'!C:C,0)&>"")"

-----

I neither tested nor verified the formula, just tried to fix it.

Regards!

 

[ssuresh98]

Hi SirJB7,

Thanks. Your formula still gave an error. You were right about the quotes. The problem was when the formula had to look up another worksheet (Master SNP Database). When I enclosed it with "" it worked. Thanks for your help.

 

[SirJB7]

Hi, ssuresh98!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!

 

[shrivallabha]

You can use the trick and code posted in this thread:

http://chandoo.org/forums/topic/concatenate-two-text-in-vba