Formula too long for VBA code - ways to shorten it or make it work in VBA?

[taikvei]

Hi,

I've got a VBA code that pulls together some phone numbers, and then concatenates them to 1 cell + removes duplicates. It is super ridiculous and long, but I couldn't find any other way to do it.

Basically the way it works, is that it will discard values if length < 3 (some will have a 0 or something), and then do a check for the last few digits of the phone number in the next numbers, and only keep it if there's no matches. When I put it in by hand originally, it worked fine, and did what I needed it to do. I then used the macro recorder and it recorded it, but when I try to run the bit that was recorded, I get the 1004 error (which according to googling is due the formula being way too long)

I am pretty sure that the formula is not anywhere near being efficient, but it does the trick

Does anyone have any ideas on how to shorten the formula or how to workaround the 1004 error? I've tried replacing """" with `, and RIGHT with "~", but it is still too long

This is what the macro recorder spat out for the formula. The formula would be in cell K2, and look at cells E2:J2

=CONCATENATE(IF(AND(RC[-6]="""",RC[-5]="""",RC[-4]="""",RC[-3]="""",RC[-2]="""",RC[-1]=""""), ""No Number"",""""),IF(OR(LEN(RC[-6])<3,RIGHT(RC[-6],6)=RIGHT(RC[-5],6),RIGHT(RC[-6],6)=RIGHT(RC[-4],6),RIGHT(RC[-6],6)=RIGHT(RC[-3],6),RIGHT(RC[-6],6)=RIGHT(RC[-2],6),RIGHT(RC[-6],6)=RIGHT(RC[-2],6)),"""",RC[-6]&CHAR(10)),IF(OR(LEN(RC[-5])<3,RIGHT(RC[-5],6)=RIGHT(RC[-4],6)" & _        "C[-5],6)=RIGHT(RC[-3],6),RIGHT(RC[-5],6)=RIGHT(RC[-2],6),RIGHT(RC[-5],6)=RIGHT(RC[-1],6)),"""",RC[-5]&CHAR(10)), IF(OR(LEN(RC[-4])<3,RIGHT(RC[-4],6)=RIGHT(RC[-3],6),RIGHT(RC[-4],6)=RIGHT(RC[-2],6),RIGHT(RC[-4],6)=RIGHT(RC[-1],6)),"""",RC[-4]&CHAR(10)),IF(OR(LEN(RC[-3])<3,RIGHT(RC[-3],6)=RIGHT(RC[-2],6),RIGHT(RC[-3],6)=RIGHT(RC[-1],6)),"""",RC[-3]&CHAR(10)),IF(OR(LEN" & _
        "3,RIGHT(RC[-2],6)=RIGHT(RC[-1],6)),"""",RC[-2]&CHAR(10)),IF(LEN(RC[-1])<3,"""",RC[-1]&CHAR(10)))"

 

[RoryA]

Select a cell with the formula in it. Then run this macro:

Sub FormulaForVBA()
    Debug.Print Chr(34) & Replace(ActiveCell.FormulaR1C1, Chr(34), Chr(34) & Chr(34)) & Chr(34)
End Sub

and it will output the correct formula string to the Immediate Window (Ctrl+G if that is not already visible in the VB Editor), and you can then copy and paste it into your code in place of the wrong one.

 

[taikvei]

Select a cell with the formula in it. Then run this macro:

Sub FormulaForVBA()
    Debug.Print Chr(34) & Replace(ActiveCell.FormulaR1C1, Chr(34), Chr(34) & Chr(34)) & Chr(34)
End Sub

and it will output the correct formula string to the Immediate Window (Ctrl+G if that is not already visible in the VB Editor), and you can then copy and paste it into your code in place of the wrong one.

The formula itself is correct, it is just far too long and I get the VBA 1004 error because of it. Will this not just spit out the same formula?
or am I misunderstanding you?

 

[taikvei]

Nope, nevermind - using the formula from debug.print works. Could you explain why that is by any chance? Is it just because it keeps it all in 1 line rather than & _ to make it in multiple lines? It looks the same apart from that..

 

[RoryA]

The macro recorder has an issue with very long formulas. When it breaks up the lines, it actually overwrites part of the formula so the result is invalid, which is why you get the 1004 error.