Missing parts of pasted formula when when recording macro.

[leemcder]

Can someone please help me? When I copy the formula below(which works) while recording a macro, it seems to have parts of the formula cut off in the code (highlighted below is what the macro creates) for example, the original formula, looks in the data sheet for "highway Tripper", as you can see in the macro recording (ive highlighted in black), it is changed to

Highw" & _
"er"")) I assume this is why the code wont work? Can someone please tell me how to stop this from happening? I'd rather not have to manually type in the formula, as I have about 15 of them.

Thanks​

=SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Clinical Negligence"))+SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Financial Mis-Selling"))+SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Highway Tripper"))+SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Holiday Illness"))+SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Industrial Disease"))+SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Other PI (Non RTA)"))+SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"SIPP"))+SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Solar"))++SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Tripping"))+SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,"Works Accident"))


Range("I5").Select
ActiveCell.FormulaR1C1 = _
"=SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Clinical Negligence""))+SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Financial Mis-Selling""))+SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Highw" & _
"er""))+SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Holiday Illness""))+SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Industrial Disease""))+SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Other " & _
"TA)""))+SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""SIPP""))+SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Solar""))++SUM(COUNTIFS(Data!C[9],RTA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Tripping""))+SUM(COUNTIFS(Da" & _
"TA!C[-8],Data!C[8],"">1/1/1900"",Data!C[12],""Yes"",Data!C[5],"""",Data!C[-3],""Works Accident""))"

 

[leemcder]

What I mean is, if they both give the same result (as a formula), you can continue building the formula in the same manner. That will drastically reduce the length of your formula, hopefully to a point where you can record it.

Thanks, yes that has worked. =SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,{"Clinical Negligence","Financial Mis-Selling","Highway Tripper","Holiday Illness","Industrial Disease","Other PI (Non RTA)","SIPP","Solar","Tripping","Works Accident"})) This gives me the same result. It will be short enough to record. Thanks so much for your help. Much appreciated

 

[leemcder]

I was thinking

=SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,OR("Clinical Negligence","Financial Mis-Selling","Highway Tripper","Holiday Illness","Industrial Disease","SIPP","Solar","Tripping","Works Accident")))

but noticed the beginning of an Array in Fluffs test


I can't test this so it may just fall over

Seem to have got there in the end! The formula below seems to work. Thanks for your time and help with this. Appreciated

=SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",Data!U:U,"Yes",Data!N:N,"",Data!F:F,{"Clinical Negligence","Financial Mis-Selling","Highway Tripper","Holiday Illness","Industrial Disease","Other PI (Non RTA)","SIPP","Solar","Tripping","Works Accident"}))​

 

[Fluff]

Glad we could help & thanks for the feedback

 

[mole999]

just one other thought > 1/1/1900 is zero in 1900 date system, unless you are going to set a definitive cut off date. And rather than code those as hard values in the VBA put a cell reference from the sheet in and you can change as necessary, same as YES