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

taikvei

New Member
Joined
Nov 29, 2018
Messages
14
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

Code:
=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)))"
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Select a cell with the formula in it. Then run this macro:

Code:
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.
 
Upvote 0
Select a cell with the formula in it. Then run this macro:

Code:
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?
 
Last edited:
Upvote 0
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..
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top