Issues with getting a long formula into a macro

[jeh0408]

Hello! First time post, but this forum has been an amazing resource. I'm a little new to the formula/coding aspect of Excel, so apologies in advance.

I'm attempting to get two separate concatenation formulas into a somewhat complex macro. I ended up using the record macro function, but there is a constant error when it gets to pasting the concatenation formula.

The relevant formulas are as follows:


=CONCATENATE("BETWEEN ",TEXT(IF(MIN(E:E) > 0, MIN(E:E), ""),"MM/DD/YYYY")," AND ",TEXT(IF(MAX(E:E) > 0, MAX(E:E), ""),"MM/DD/YYYY"),","," ",K2," ",L2," RECEIVED ",COUNTIFS(I:I,"MONEY TRANSFER RECV",Y:Y,"<>AUTOMATED",Y:Y,"<>MONEYGRAM")," TRANSACTIONS FROM X SENDERS IN X, TOTALING ","$",SUMIFS(J:J,I:I,"MONEY TRANSFER RECV",Y:Y,"<>AUTOMATED"),", AND SENT"," ",COUNTIF(I:I,"MONEY TRANSFER SEND")," TRANSACTIONS TO X RECIPIENTS IN X TOTALING ","$",SUMIFS(J:J,I:I,"MONEY TRANSFER SEND",Y:Y,"<>AUTOMATED"),".")


=CONCATENATE("BETWEEN ",TEXT(IF(MIN(E:E) > 0, MIN(E:E), ""),"MM/DD/YYYY")," AND ",TEXT(IF(MAX(E:E) > 0, MAX(E:E), ""),"MM/DD/YYYY"),", ",K2," ",L2," CONDUCTED"," ",COUNTIFS(I:I,"*",I:I,"<>Product Type",Y:Y,"<>AUTOMATED")," TRANSACTIONS TOTALING ","$",SUMIFS(J:J,Y:Y,"<>AUTOMATED",Y:Y,"<>MONEYGRAM>")," WITH X COUNTERPARTIES IN ",Stringconcat(", ",Ao2:Ao1000), ".")



Even after the VBA auto-formatting. I've tried adding quotation marks (though I end up going down a rabbit hole when I do this) and pasting the formula in line by line and using the appropriate separators.

Thank you all!

 

[bfreescott]

Welcome to the board! I don't know that this will solve the issue you are having, but off the top of my head, I want to say make sure you are properly using Underscore to tell VBA that you are placing one line of code on multiple lines. Like this...

=CONCATENATE("BETWEEN ",TEXT(IF(MIN(E:E) > 0, MIN(E:E),  ""),"MM/DD/YYYY")," AND ",TEXT(IF(MAX(E:E) > 0,_
 MAX(E:E),  ""),"MM/DD/YYYY"),", ",K2," ",L2," CONDUCTED","  ",COUNTIFS(I:I,"*",I:I,"<>Product Type",Y:Y,"<>AUTOMATED"),"  TRANSACTIONS TOTALING  ","$",SUMIFS(J:J,Y:Y,"_
<>AUTOMATED",Y:Y,"<>MONEYGRAM>"),"  WITH X COUNTERPARTIES IN ",Stringconcat(", ",Ao2:Ao1000),_
 ".")

 

[jeh0408]

Thanks so much for the response!

Unfortunately, I'm beginning to see that the line breaks aren't necessarily the issue. I think it's down to the quotation marks: When are those needed exactly in formulas like this?

 

[bfreescott]

I think we need to see a bit more of your code. Are you trying to get VBA to calculate the formula or are you just using VBA to place a formula for you using Application.WorksheetFunction

 

[jeh0408]

Hi!

I am doing the latter - I put a different equation into the VBA code and it functioned perfectly.

The relevant portion of the code is:

    Range("D2").End(xlDown).Select
    ActiveCell.Offset(2, 0).Select
    ActiveCell.Formula = _
        "=CONCATENATE("BETWEEN ",TEXT(IF(MIN(E:E) > 0, MIN(E:E), ""),"MM/DD/YYYY")," AND ",TEXT(IF(MAX(E:E) > 0, MAX(E:E), ""),"MM/DD/YYYY"),","," ",K2," ",L2," RECEIVED ",COUNTIFS(I:I,"MONEY TRANSFER RECV",Y:Y,"<>AUTOMATED",Y:Y,"<>MONEYGRAM")," TRANSACTIONS FROM X SENDERS IN ",Stringconcat(", ",(AQ2:AQ100))," TOTALING ","$",SUMIFS(J:J,I:I,"MONEY TRANSFER RECV",Y:Y,"<>AUTOMATED"),", AND SENT"," ",COUNTIF(I:I,"MONEY TRANSFER SEND")," TRANSACTIONS TO X RECIPIENTS IN ",Stringconcat(", ",(AO2:AO100))," TOTALING ","$",SUMIFS(J:J,I:I,"MONEY TRANSFER SEND",Y:Y,"<>AUTOMATED"),".")"
    ActiveCell.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D2").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Formula = _
        "=CONCATENATE("BETWEEN ",TEXT(IF(MIN(E:E) > 0, MIN(E:E), ""),"MM/DD/YYYY")," AND ",TEXT(IF(MAX(E:E) > 0, MAX(E:E), ""),"MM/DD/YYYY"),", ",K2," ",L2," CONDUCTED"," ",COUNTIFS(I:I,"*",I:I,"<>Product Type",Y:Y,"<>AUTOMATED")," TRANSACTIONS TOTALING ","$",SUMIFS(J:J,Y:Y,"<>AUTOMATED",Y:Y,"<>MONEYGRAM>")," WITH X COUNTERPARTIES IN ",Stringconcat(", ",(AR2:AR100)),".")
    ActiveCell.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I've just pasted the formulas directly in there.