VBA formula not writing when going onto a second line of code

[theglitch]

Hi Experts,

I have been working for hours on this and have had no luck in getting this to write to the Excel file. I have included the code below and what the formula should be looking like in the Excel file. Any help or thoughts would be greatly appreciated. Also, please let me know what else you need from me. The problem is that the formula will not fit on one single line in the VBA editor. I have tried the (" & _) in all different combos.

Cells(j, i).Formula = "=IF((SUMIFS(" & Cells(StartRow, "K").Address & ":" & Cells(j - 1, i).Address & "," & Cells(StartRow, "A").Address & ":" & Cells(j - 1, "A").Address & ",""D""," & Cells(StartRow, "J").Address & ":" & Cells(j - 1, "J").Address & ",""H"")-SUMIFS(" & Cells(StartRow, "K").Address & ":" & Cells(j - 1, i).Address & "," & Cells(StartRow, "A").Address & ":" & Cells(j - 1, "A").Address & ",""D""," & Cells(StartRow, "J").Address & ":" & Cells(j - 1, "J").Address & ",""S""))<0,SUMIFS(" & Cells(StartRow, "K").Address & ":" & Cells(j - 1, i).Address & "," & Cells(StartRow, "A").Address & ":" & Cells(j - 1, "A").Address & ",""D""," & Cells(StartRow, "J").Address & ":" & Cells(j - 1, "J").Address & ",""H"")-SUMIFS(" & Cells(StartRow, "K").Address & ":" & Cells(j - 1, i).Address & "," & Cells(StartRow, "A").Address & ":" & Cells(j - 1, "A").Address & ",""D""," & Cells(StartRow, "J").Address & ":" & Cells(j - 1, "J").Address & ",""S"")*-1)," _                                          & "(SUMIFS(" & Cells(StartRow, "K").Address & ":" & Cells(j - 1, i).Address & "," & Cells(StartRow, "A").Address & ":" & Cells(j - 1, "A").Address & ",""D""," & Cells(StartRow, "J").Address & ":" & Cells(j - 1, "J").Address & ",""H"")-SUMIFS(" & Cells(StartRow, "K").Address & ":" & Cells(j - 1, i).Address & "," & Cells(StartRow, "A").Address & ":" & Cells(j - 1, "A").Address & ",""D""," & Cells(StartRow, "J").Address & ":" & Cells(j - 1, "J").Address & ",""S"")))"

What the formula should show :=IF((SUMIFS(K13:K39,A13:A39,"D",J13:J39,"H")-SUMIFS(K13:K39,A13:A39,"D",J13:J39,"S"))<0,(SUMIFS(K13:K39,A13:A39,"D",J13:J39,"H")-SUMIFS(K13:K39,A13:A39,"D",J13:J39,"S")*-1),(SUMIFS(K13:K39,A13:A39,"D",J13:J39,"H")-SUMIFS(K13:K39,A13:A39,"D",J13:J39,"S")))

 

[John_w]

For long formulas like this, I create a 'template' with the variable parts of the formula represented as [name] and then have lines of code which replace each [name] with the computed cell addresses to create the final formula. Also, because VBA uses two double quote characters to represent one double quote character inside a string, it is easier and more readable to use the single quote character (apostrophe) instead of the double quote character, and have a line which replaces the former with the latter.

    Dim f As String
    
    f = "=IF((SUMIFS([KCELLS],[ACELLS],'D',[JCELLS],'H')-SUMIFS([KCELLS],[ACELLS],'D',[JCELLS],'S'))<0,(SUMIFS([KCELLS],[ACELLS],'D',[JCELLS],'H')-SUMIFS([KCELLS],[ACELLS],'D',[JCELLS],'S')*-1),(SUMIFS([KCELLS],[ACELLS],'D',[JCELLS],'H')-SUMIFS([KCELLS],[ACELLS],'D',[JCELLS],'S')))"
    f = Replace(f, "[KCELLS]", Cells(startRow, "K").Address & ":" & Cells(j - 1, i).Address)
    f = Replace(f, "[ACELLS]", Cells(startRow, "A").Address & ":" & Cells(j - 1, "A").Address)
    f = Replace(f, "[JCELLS]", Cells(startRow, "J").Address & ":" & Cells(j - 1, "J").Address)
    f = Replace(f, "'", Chr(34))  'change single quote to double quote
    f = Replace(f, "$", "")       'remove absolute addresses
    Cells(j, i).Formula = f

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...ng-when-going-onto-a-second-line-of-code.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[6StringJazzer]

Solved at the cross-posted location. (Missing paren in the VBA version)