Recording a long formula is splitting it and destroying it

[RockandGrohl]

Hi all, I'm trying to record a sumif with 6 different criteria pointing at different workbooks, when I record it, it seems to cut it after a point and replace a bunch of it with a " _ & "

As a result, I think I fixed it with this but I still get application or object defined error:

Range(Cells(arow, ActiveCell.Offset(0, 1).Column), Cells(respend, ActiveCell.Offset(0, 1).Column)).FormulaR1C1 = "=SUMIFS('[Faculty Reporting Master.xlsm]Booking Data'!C6,'[Faculty Reporting Master.xlsm]Booking Data'!C13,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C14,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C17,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C9,"">=""&DATE(RC1,RC2,1),'[Faculty Reporting Master.xlsm]Booking Data'!C9,""<=""&EOMONTH(DATE(RC1,RC2,1)0),'[Faculty Reporting Master.xlsm]Booking Data'!C21,R2C[-1])"

Just to note, the formula above it which is just too short to get chopped up works fine:

Range(Cells(arow, ActiveCell.Column), Cells(respend, ActiveCell.Column)).FormulaR1C1 = "=COUNTIFS('[Faculty Reporting Master.xlsm]Booking Data'!C13,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C14,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C17,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C9,"">=""&DATE(RC1,RC2,1),'[Faculty Reporting Master.xlsm]Booking Data'!C9,""<=""&EOMONTH(DATE(RC1,RC2,1),0),'[Faculty Reporting Master.xlsm]Booking Data'!C21,R2C)"

And if I put that formula in the space for troubled line of code, it works fine (so it's not a problem with the activecell.offset part of the code)

Any ideas? Thanks.

 

[Joe4]

Hi all, I'm trying to record a sumif with 6 different criteria pointing at different workbooks, when I record it, it seems to cut it after a point and replace a bunch of it with a " _ & "

It should be fine, the way that it is. The "_ &" are line continuation characters, indicating that the formula is continued on the next line.

If you record a working formula, and you see that in the VBA code, the formula should work fine, and you shouldn't need to edit it.

 

[RoryA]

The macro recorder is flawed with long formulas - it seems to replace part of the actual formula string with the line continuation characters. I suggest you enter the formula into the first cell manually and then with that cell active, run this:

Public Sub CopyCellContents_Sub()
   Dim objData           As Object
   ' this is a late bound MSForms.DataObject
   Set objData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

   'Same as f2+copy contents.
'    OpenClipboard 0&
   With objData
      .SetText Replace(ActiveCell.FormulaR1C1, """", """""")
      .PutInClipboard
   End With

End Sub

which will copy the correct formula string (in R1C1 format) to the clipboard so that you can paste it into the VB Editor.

 

[RockandGrohl]

Fixed, the problem code now looks like this:

Range(Cells(arow, ActiveCell.Offset(0, 1).Column), Cells(respend, ActiveCell.Offset(0, 1).Column)).FormulaR1C1 = "=SUMIFS('[Faculty Reporting Master.xlsm]Booking Data'!C6,'[Faculty Reporting Master.xlsm]Booking Data'!C13,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C14,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C17,""Y"",'[Faculty Reporting Master.xlsm]Booking Data'!C9,"">=""&DATE(RC1,RC2,1),'[Faculty Reporting Master.xlsm]Booking Data'!C9,""<=""&EOMONTH(DATE(RC1,RC2,1),0),'[Faculty Reporting Master.xlsm]Booking Data'!C21,R2C[-1])"

 

[RockandGrohl]

The macro recorder is flawed with long formulas - it seems to replace part of the actual formula string with the line continuation characters. I suggest you enter the formula into the first cell manually and then with that cell active, run this:

Public Sub CopyCellContents_Sub()
   Dim objData           As Object
   ' this is a late bound MSForms.DataObject
   Set objData = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

   'Same as f2+copy contents.
'    OpenClipboard 0&
   With objData
      .SetText Replace(ActiveCell.FormulaR1C1, """", """""")
      .PutInClipboard
   End With

End Sub

which will copy the correct formula string (in R1C1 format) to the clipboard so that you can paste it into the VB Editor.

Hi Rory,

I actually solved it because the COUNTIF and the SUMIF had mostly the same criteria, but the end of the SUMIF was being snipped off so I replaced it with the end of the COUNTIF.

It was some kind of reference that was being trampled in the DATE part of the formula.

However, this looks really helpful so I will bookmark it for future reference, and as always, thank you for your time.

 

[Joe4]

The macro recorder is flawed with long formulas - it seems to replace part of the actual formula string with the line continuation characters.

I stand corrected. I guess all the formulas I have recorded over the years have never been long enough to run into this issue!
Sorry for any confusion I may have caused.