Unable to set the FormulaArray property of a range class

[DRV21]

Hello,

I have the following formula which i know it exceeds 255 characters but i dont know how to divide it in order to be valid.

Ill attach the macro for a better understanding :

Sub Macro2()

Dim playerReportSheet As Worksheet

Set playerReportSheet = ThisWorkbook.Worksheets("Player report")
Dim formulaPart1 As String
formulaPart1 = "=IF(('All transactions'!$C$3:C10000>='Player report'!F3)*('All transactions'!$C$3:C10000<'Player report'!F4)*('All transactions'!$B$3:B10000>='Player report'!$C$2)*('All transactions'!$B$3:B10000<'Player report'!$E$2),'All transactions'!$L$3:L10000)"
playerReportSheet.Range("G3").FormulaArray = formulaPart1
playerReportSheet.Range("G3").AutoFill Destination:=playerReportSheet.Range("G3:G26")
playerReportSheet.Range("G3:H26").NumberFormat = "#,##0.00"
playerReportSheet.Range("I3:I26").NumberFormat = "[Red]#,##0.00;[Color10]#,##0.00;General"
End Sub

I know there's a way by using replace but i couldn't divide the formula in order to be valid. If there's someone who can help, please.

 

[DRV21]

Sub Macro2()

Dim playerReportSheet As Worksheet

Set playerReportSheet = ThisWorkbook.Worksheets("Player report")
Dim formulaPart1 As String
formulaPart1 = "=SUM(IF(('All transactions'!$C$3:C10000>='Player report'!F3)*('All transactions'!$C$3:C10000<'Player report'!F4)*('All transactions'!$B$3:B10000>='Player report'!$C$2)*('All transactions'!$B$3:B10000<'Player report'!$E$2),'All transactions'!$L$3:L10000))"
playerReportSheet.Range("G3").FormulaArray = formulaPart1
playerReportSheet.Range("G3").AutoFill Destination:=playerReportSheet.Range("G3:G26")
playerReportSheet.Range("G3:H26").NumberFormat = "#,##0.00"
playerReportSheet.Range("I3:I26").NumberFormat = "[Red]#,##0.00;[Color10]#,##0.00;General"
End Sub

Updated formula, i misscopied.

 

[Fluff]

Hi & welcome to MrExcel.
As the formula is going on the "Player Report" sheet use

"=SUM(IF(('All transactions'!$C$3:C10000>=F3)*('All transactions'!$C$3:C10000<F4)*('All transactions'!$B$3:B10000>=$C$2)*('All transactions'!$B$3:B10000<$E$2),'All transactions'!$L$3:L10000))"

You should never refer to the name of the sheet the formula is on as it can cause problems.

Also in future when posting code, please use code tags, not quotes. How to Post Your VBA Code

 

[DRV21]

Hi & welcome to MrExcel.
As the formula is going on the "Player Report" sheet use

"=SUM(IF(('All transactions'!$C$3:C10000>=F3)*('All transactions'!$C$3:C10000<F4)*('All transactions'!$B$3:B10000>=$C$2)*('All transactions'!$B$3:B10000<$E$2),'All transactions'!$L$3:L10000))"

You should never refer to the name of the sheet the formula is on as it can cause problems.

Also in future when posting code, please use code tags, not quotes. How to Post Your VBA Code

Hello, indeed that did the trick by lowering the nr of characters. Easy solution which ofc i didnt thought.

Thank you and yes, i'll use code tag for future posts.

 

[Fluff]

Thinking about it, if every one who uses that workbook is using 2012 or 365 you could use

Sub Macro2()

With ThisWorkbook.Worksheets("Player report")
   .Range("G3:G26").Formula2 = "=SUM(IF(('All transactions'!$C$3:C10000>=F3)*('All transactions'!$C$3:C10000<F4)*('All transactions'!$B$3:B10000>=$C$2)*('All transactions'!$B$3:B10000<$E$2),'All transactions'!$L$3:L10000))"
   .Range("G3:H26").NumberFormat = "#,##0.00"
   .Range("I3:I26").NumberFormat = "[Red]#,##0.00;[Color10]#,##0.00;General"
End With
End Sub