Too Long of a formula for a macro?

**melnemac32** · 10-08-2013, 02:07 PM

Hello,

I have a formula that I spent siginficant time trying to develop, but when I try to paste it into my macro I think that it is to long. When I try to paste it is highlighted in red because the ActiveCell.FormulaR1C1 = is not on the same line as the formula. The formula itself fits on one line. Please help me figure out a way to paste it into the macro. I don't want to start from scratch with my formula

Here is the formula:

=IF(AND(NOT(D2="All"),NOT(E2="All")),COUNTIFS('TS Data'!E2:E60000,">="&B2,'TS Data'!E2:E60000,"<="&C2,'TS Data'!P2:P60000,D2,'TS Data'!O2:O60000,E2,'TS Data'!K2:K60000,"Yes"),IF(AND(D2="All",NOT(E2="All")),COUNTIFS('TS Data'!E2:E60000,">="&B2,'TS Data'!E2:E60000,"<="&C2,'TS Data'!O2:O60000,E2,'TS Data'!K2:K60000,"Yes"),IF(AND(NOT(D2="All"),E2="All"),COUNTIFS('TS Data'!E2:E60000,">="&B2,'TS Data'!E2:E60000,"<="&C2,'TS Data'!P2:P60000,D2,'TS Data'!K2:K60000,"Yes"),COUNTIFS('TS Data'!E2:E60000,">="&B2,'TS Data'!E2:E60000,"<="&C2,'TS Data'!F2:F60000,"Yes",'TS Data'!K2:K60000,"Yes"))))

Here is the code

ActiveCell.FormulaR1C1 =
"=IF(AND(NOT(R[-3]C[-2]=""All""),NOT(R[-3]C[-1]=""All"")),COUNTIFS('TS Data'!R[-3]C[-1]:R[59995]C[-1],"">=""&R[-3]C[-4],'TS Data'!R[-3]C[-1]:R[59995]C[-1],""<=""&R[-3]C[-3],'TS Data'!R[-3]C[10]:R[59995]C[10],R[-3]C[-2],'TS Data'!R[-3]C[9]:R[59995]C[9],R[-3]C[-1],'TS Data'!R[-3]C[5]:R[59995]C[5],""Yes""),IF(AND(R[-3]C[-2]=""All"",NOT(R[-3]C[-1]=""All"")),COUNTIFS('TS Data'!R[-3]C[-1]:R[59995]C[-1],"">=""&R[-3]C[-4],'TS Data'!R[-3]C[-1]:R[59995]C[-1],""<=""&R[-3]C[-3],'TS Data'!R[-3]C[9]:R[59995]C[9],R[-3]C[-1],'TS Data'!R[-3]C[5]:R[59995]C[5],""Yes""),IF(AND(NOT(R[-3]C[-2]=""All""),R[-3]C[-1]=""All""),COUNTIFS('TS Data'!R[-3]C[-1]:R[59995]C[-1],"">=""&R[-3]C[-4],'TS Data'!R[-3]C[-1]:R[59995]C[-1],""<=""&R[-3]C[-3], 'TS Data'!R[-3]C[10]:R[59995]C[10],R[-3]C[-2],'TS Data'!R[-3]C[5]:R[59995]C[5],""Yes""),COUNTIFS('TS Data'!R[-3]C[-1]:R[59995]C[-1],"">=""&R[-3]C[-4],'TS Data'!R[-3]C[-1]:R[59995]C[-1],""<=""&R[-3]C[-3],'TS Data'!R[-3]C:R[59995]C,""Yes"",'TS Data'!R[-3]C[5]:R[59995]C[5],""Yes""))))"

Any ideas on what I can do to get it to fit into a macro?

**TMS** · 10-08-2013, 02:30 PM

Sub test()
ActiveCell.FormulaR1C1 = _
    "=IF(AND(NOT(R[-3]C[-2]=""All""),NOT(R[-3]C[-1]=""All"")),COUNTIFS('TS Data'!R[-3]C[-1]:R[59995]C[-1],"">=""&R[-3]C[-4],'TS Data'!R[-3]C[-1]:R[59995]C[-1],""<=""&R[-3]C[-3],'TS Data'!R[-3]C[10]:R[59995]C[10],R[-3]C[-2],'TS Data'!R[-3]C[9]:R[59995]C[9],R[-3]C[-1],'TS Data'!R[-3]C[5]:R[59995]C[5],""Yes""),IF(AND(R[-3]C[-2]=""All"",NOT(R[-3]C[-1]=""All"")),COUNTIFS('TS Data'!R[-3]C[-1]:R[59995]C[-1],"">=""&R[-3]C[-4],'TS Data'!R[-3]C[-1]:R[59995]C[-1],""<=""&R[-3]C[-3],'TS Data'!R[-3]C[9]:R[59995]C[9],R[-3]C[-1],'TS Data'!R[-3]C[5]:R[59995]C[5],""Yes""),IF(AND(NOT(R[-3]C[-2]=""All""),R[-3]C[-1]=""All""),COUNTIFS('TS Data'!R[-3]C[-1]:R[59995]C[-1],"">=""&R[-3]C[-4],'TS Data'!R[-3]C[-1]:R[59995]C[-1],""<=""&R[-3]C[-3], 'TS Data'!R[-3]C[10]:R[59995]C[10],R[-3]C[-2],'TS Data'!R[-3]C[5]:R[59995]C[5],""Yes""),COUNTIFS('TS Data'!R[-3]C[-1]:R[59995]C[-1],"">=""&R[-3]C[-4],'TS Data'!R[-3]C[-1]:R[59995]C[-1],""<=""&R[-3]C[-3],'TS Data'!R[-3]C:R[59995]C,""Yes"",'TS Data'!R[-3]C[5]:R[59995]C[5],""Yes""))))"
End Sub

Note the underscore.

Regards, TMS