VBA: How to make macro for typing specific (long) formula in cell ?

Hi guys,

I'm trying to make a macro to type a formula presented in end of thread. Its very long formula, so I'd like a macro that could insert it (have to use the formula to update update quite frequently, it basically merges values from another document). I'm thinking I could assign the long formula as a variable or whatever and then just run it for a cell range whenever I wish.


FYI, especially the " " in the formula gives me problems defining the formula as a variable - I'm not super experienced in VBA.

this is the formula, probably doesn't make sense to you, but it works as for merging from another spreadsheet:

=IF(IFERROR(YEAR(INDEX('[LE Smart Table2.xls]Sheet1'!$H$2:$H$2000;MATCH($C$2:$C$2000;'[LE Smart Table2.xls]Sheet1'!$E$2:$E$2000;0)))&" Q"&INT((MONTH(INDEX('[LE Smart Table2.xls]Sheet1'!$H$2:$H$2000;MATCH($C$2:$C$2000;'[LE Smart Table2.xls]Sheet1'!$E$2:$E$2000;0)))-1)/3+1);"")="1900 Q1";"";IFERROR(YEAR(INDEX('[LE Smart Table2.xls]Sheet1'!$H$2:$H$2000;MATCH($C$2:$C$2000;'[LE Smart Table2.xls]Sheet1'!$E$2:$E$2000;0)))&" Q"&INT((MONTH(INDEX('[LE Smart Table2.xls]Sheet1'!$H$2:$H$2000;MATCH($C$2:$C$2000;'[LE Smart Table2.xls]Sheet1'!$E$2:$E$2000;0)))-1)/3+1);""))

Originally Posted by TMS

You need to double up the quotes in the formula:

Dim x As String
x = "=IF(IFERROR(YEAR(INDEX('[LE Smart Table2.xls]Sheet1'!$H$2:$H$2000;MATCH($C$2:$C$2000;'[LE Smart Table2.xls]Sheet1'!$E$2:$E$2000;0)))&"" Q""&INT((MONTH(INDEX('[LE Smart Table2.xls]Sheet1'!$H$2:$H$2000;MATCH($C$2:$C$2000;'[LE Smart Table2.xls]Sheet1'!$E$2:$E$2000;0)))-1)/3+1);"""")=""1900 Q1"";"""";IFERROR(YEAR(INDEX('[LE Smart Table2.xls]Sheet1'!$H$2:$H$2000;MATCH($C$2:$C$2000;'[LE Smart Table2.xls]Sheet1'!$E$2:$E$2000;0)))&"" Q""&INT((MONTH(INDEX('[LE Smart Table2.xls]Sheet1'!$H$2:$H$2000;MATCH($C$2:$C$2000;'[LE Smart Table2.xls]Sheet1'!$E$2:$E$2000;0)))-1)/3+1);""""))"
MsgBox x

Regards, TMS

TMS, thank you, the string works.

Can you tell me how I make a macro that would insert the string in a specific cell?