Syntax Error in VBA and Cannot save .xlsx with Macro - Formulas too long I think?

I tried recording a quick macro yesterday. I recorded the processes to pull down data from the web, delete some rows, add some new fields and crunch some data. I then succesfully stopped recording the macro (no errors at this stage). I then tried to save the .xlsx, but i get an error that the document has errors that cannot be repaired. When I hit "OK", it says "Document not saved".

I have isolated the error down to 2 of the formulas I am using (the macro is ok and the document saves when I leave these 2 formulas out). Both of these formulas are pretty long - one is 1,800 characters, one is 800 characters. When editing these 2 formulas in VBA, I notice that they span across multiple lines, and every line after the first appears in red in VBA. If I tried chopping it up across the multiple lines with " _" but I get syntax error.

After some research, I have found multiple posts saying that there is a character limitation to formulas used in macros. Is there any work around to the character limitation? (I assume someone will answer that the workaround is to write a more efficient formula!).

If anyone knows a workaround, great. If not, I'd be happy to share my formulas (see below) and what the data looks like for a suggestion on how to better write the formulas (which is probably the solution that will better benefit me in the long run anyway!)

Thanks!


Formula 1: Purpose: If O6 is not blank, then I want the formula to cycle through from B7 to N7 and look for the numbers 1 through 13. Any missing numbers from 1 to 13 should be written with commas in between them into U6. If O6 is blank, then I want U6 to also be blank. The formula is then filled from U6:U66 to do the same thing in each respective row.

=IF(AND(O6<>"", B7<>-13, C7<>-13, D7<>-13, E7<>-13, F7<>-13, G7<>-13, H7<>-13, I7<>-13, J7<>-13, K7<>-13, L7<>-13, M7<>-13, N7<>-13), 13, "") & ", " & IF(AND(O6<>"", B7<>-12, C7<>-12, D7<>-12, E7<>-12, F7<>-12, G7<>-12, H7<>-12, I7<>-12, J7<>-12, K7<>-12, L7<>-12, M7<>-12, N7<>-12), 12, "") & ", " & IF(AND(O6<>"", B7<>-11, C7<>-11, D7<>-11, E7<>-11, F7<>-11, G7<>-11, H7<>-11, I7<>-11, J7<>-11, K7<>-11, L7<>-11, M7<>-11, N7<>-11), 11, "") & ", " & IF(AND(O6<>"", B7<>-10, C7<>-10, D7<>-10, E7<>-10, F7<>-10, G7<>-10, H7<>-10, I7<>-10, J7<>-10, K7<>-10, L7<>-10, M7<>-10, N7<>-10), 10, "") & ", " & IF(AND(O6<>"", B7<>-9, C7<>-9, D7<>-9, E7<>-9, F7<>-9, G7<>-9, H7<>-9, I7<>-9, J7<>-9, K7<>-9, L7<>-9, M7<>-9, N7<>-9), 9, "") & ", " & IF(AND(O6<>"", B7<>-8, C7<>-8, D7<>-8, E7<>-8, F7<>-8, G7<>-8, H7<>-8, I7<>-8, J7<>-8, K7<>-8, L7<>-8, M7<>-8, N7<>-8), 8, "") & ", " & IF(AND(O6<>"", B7<>-7, C7<>-7, D7<>-7, E7<>-7, F7<>-7, G7<>-7, H7<>-7, I7<>-7, J7<>-7, K7<>-7, L7<>-7, M7<>-7, N7<>-7), 7, "") & ", " & IF(AND(O6<>"", B7<>-6, C7<>-6, D7<>-6, E7<>-6, F7<>-6, G7<>-6, H7<>-6, I7<>-6, J7<>-6, K7<>-6, L7<>-6, M7<>-6, N7<>-6), 6, "") & ", " & IF(AND(O6<>"", B7<>-5, C7<>-5, D7<>-5, E7<>-5, F7<>-5, G7<>-5, H7<>-5, I7<>-5, J7<>-5, K7<>-5, L7<>-5, M7<>-5, N7<>-5), 5, "") & ", " & IF(AND(O6<>"", B7<>-4, C7<>-4, D7<>-4, E7<>-4, F7<>-4, G7<>-4, H7<>-4, I7<>-4, J7<>-4, K7<>-4, L7<>-4, M7<>-4, N7<>-4), 4, "") & ", " & IF(AND(O6<>"", B7<>-3, C7<>-3, D7<>-3, E7<>-3, F7<>-3, G7<>-3, H7<>-3, I7<>-3, J7<>-3, K7<>-3, L7<>-3, M7<>-3, N7<>-3), 3, "") & ", " & IF(AND(O6<>"", B7<>-2, C7<>-2, D7<>-2, E7<>-2, F7<>-2, G7<>-2, H7<>-2, I7<>-2, J7<>-2, K7<>-2, L7<>-2, M7<>-2, N7<>-2), 2, "") & ", " & IF(AND(O6<>"", B7<>-1, C7<>-1, D7<>-1, E7<>-1, F7<>-1, G7<>-1, H7<>-1, I7<>-1, J7<>-1, K7<>-1, L7<>-1, M7<>-1, N7<>-1), 1, "")



Formula 2: Purpose: If O6 is not blank, then I want the formula to find the row with the value "Mangina's Last Year" in A2:A68, compare that row's values (strings) in columns 2 through 14 (B through N), and give me a sum of how many times the values in the row of interest (in this case, row 6) differ. I then fill the formula from V6:V66. The first row of this data will always occur in Row 6. The column that contains "Mangina's Last Year" will always occur in Column A. The row that contains "Mangina's Last Year" will always be different, hence the VLOOKUP.

=IF(O6<>0,IF(B6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,2,FALSE)),1,0)+IF(C6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,3,FALSE)),1,0)+IF(D6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,4,FALSE)),1,0)+IF(E6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,5,FALSE)),1,0)+IF(F6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,6,FALSE)),1,0)+IF(G6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,7,FALSE)),1,0)+IF(H6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,8,FALSE)),1,0)+IF(I6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,9,FALSE)),1,0)+IF(J6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,10,FALSE)),1,0)+IF(K6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,11,FALSE)),1,0)+IF(L6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,12,FALSE)),1,0)+IF(M6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,13,FALSE)),1,0)+IF(N6<>(VLOOKUP("Mangina's Last Year",A$2:N$68,14,FALSE)),1,0),"")

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

This isn't that much shorter, but it's a whole lot fewer calculations. You can use this in U6 and copy down.

=IF(ISNUMBER(MATCH(-1,$B7:$N7,0)), "","1,") & IF(ISNUMBER(MATCH(-2,$B7:$N7,0)), "", "2,") & IF(ISNUMBER(MATCH(-3,$B7:$N7,0)), "", "3,") & IF(ISNUMBER(MATCH(-4,$B7:$N7,0)), "", "4,") & IF(ISNUMBER(MATCH(-5,$B7:$N7,0)), "","5,") & IF(ISNUMBER(MATCH(-6,$B7:$N7,0)), "", "6,") & IF(ISNUMBER(MATCH(-7,$B7:$N7,0)), "", "7,") & IF(ISNUMBER(MATCH(-8,$B7:$N7,0)), "", "8,") & IF(ISNUMBER(MATCH(-9,$B7:$N7,0)), "", "9,") & IF(ISNUMBER(MATCH(-10,$B7:$N7,0)), "", "10,") & IF(ISNUMBER(MATCH(-11,$B7:$N7,0)), "", "11,") & IF(ISNUMBER(MATCH(-12,$B7:$N7,0)), "", "12,") & IF(ISNUMBER(MATCH(-13,$B7:$N7,0)), "", "13")

I don't actually comprehend your logic for column V.



=========
Or this for a cleaner display:
=SUBSTITUTE(TRIM(IF(ISNUMBER(MATCH(-1,$B7:$N7,0)), "","1 ") & IF(ISNUMBER(MATCH(-2,$B7:$N7,0)), "", "2 ") & IF(ISNUMBER(MATCH(-3,$B7:$N7,0)), "", "3 ") & IF(ISNUMBER(MATCH(-4,$B7:$N7,0)), "", "4 ") & IF(ISNUMBER(MATCH(-5,$B7:$N7,0)), "","5 ") & IF(ISNUMBER(MATCH(-6,$B7:$N7,0)), "", "6 ") & IF(ISNUMBER(MATCH(-7,$B7:$N7,0)), "", "7 ") & IF(ISNUMBER(MATCH(-8,$B7:$N7,0)), "", "8 ") & IF(ISNUMBER(MATCH(-9,$B7:$N7,0)), "", "9 ") & IF(ISNUMBER(MATCH(-10,$B7:$N7,0)), "", "10 ") & IF(ISNUMBER(MATCH(-11,$B7:$N7,0)), "", "11 ") & IF(ISNUMBER(MATCH(-12,$B7:$N7,0)), "", "12 ") & IF(ISNUMBER(MATCH(-13,$B7:$N7,0)), "", "13")), " ", ", ")

Your analysis is very good. I love to use the Evaluate Formula tool on the Formula Auditing toolbar to watch formulas like that unfold one calc at a time.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.