Referencing set variables and ranges in array formula

[rachexcel]

Hello,

I'm having a problem referencing set variables and ranges in my formula:

    Selection.FormulaArray = _
        "=AVERAGE(IF(MONTH(Data!C2:C2315)=11,Data!X2:X2315))"

This works fine but I would like it to be dynamic as the data range and condition changes.

I would like the '=11' condition to be equal to '=month_value' which is a defined integer and the ranges 'Data!C2:C2315' & 'Data!X2:X2315' to be labelled, defined ranges such as 'range_1' and 'range_2'.

I'm having trouble with the syntax. If anyone could help, I'd be very grateful!

Thanks,
Rach

 

[pgc01]

Hi Rach
Welcome to the board

Try, for ex.:

Sub Test()
Dim r As Range, rDate As Range, rValue As Range
Dim lMonth As Long

Set r = Worksheets("Sheet1").Range("A2")
Set rDate = Worksheets("Data").Range("B2:B10")
Set rValue = Worksheets("Data").Range("C2:C10")
lMonth = 11

r.FormulaArray = "=AVERAGE(IF(MONTH(" & rDate.Address(ReferenceStyle:=xlR1C1, External:=True) & _
        ")=" & lMonth & "," & rValue.Address(ReferenceStyle:=xlR1C1, External:=True) & "))"

End Sub

 

[rachexcel]

Hello,

Thank you very much for both the welcome and the smashing response! It worked perfectly.


I tried to alter it slightly for a different part of my worksheet which has an additional if statement in:

Selection.FormulaArray = "=AVERAGE(IF(MONTH(" & D.Address(ReferenceStyle:=xlA1, External:=True) & _
        ")=" & month_value & ",IF(" & AI.Address(ReferenceStyle:=x1A1, External:=True) & _
        "=" & AdditonalInfo & "," & T.Address(ReferenceStyle:=xlA1, External:=True) & "))"

I get the error message 'Unable to set the FormulaArray property of the Range class' which I've found means that there are too many characters in the formula. I've shortened it as much as possible and I'm still getting the message.

Do you have any ideas??

Thanks,
Rach

 

[pgc01]

The FormulaArray property does not accept a string with more than 255 characters directly.

Does the right side of the statement you posted evaluate to a string with more than 255 characters?
Can you post it?

If you really need a big array formula you can always start by writing a smaller formula, for ex. with just the outer If and then execute a .Replace() and insert the inner If().

 

[rachexcel]

Here it is:

"=AVERAGE(IF(MONTH(" & D.Address(ReferenceStyle:=xlA1, External:=True) & ")=" & month_value & ",IF(" & AI.Address(ReferenceStyle:=x1A1, External:=True) & "=" & AdditonalInfo & "," & T.Address(ReferenceStyle:=xlA1, External:=True) & "))"

Even with the spaces it's only 236 characters.

Thanks for looking,

Rach

 

[pgc01]

Sorry, that's not the length excel sees, it will see the result formula.

For ex., your variable AdditonalInfo could be a string value with 300 characters.

Try before that statement:

Dim s as String

s = "=AVERAGE(IF(MONTH(" & D.Address(ReferenceStyle:=xlA1,  External:=True) & ")=" & month_value & ",IF(" &  AI.Address(ReferenceStyle:=x1A1, External:=True) & "=" &  AdditonalInfo & "," & T.Address(ReferenceStyle:=xlA1,  External:=True) & "))"

Msgbox s

This gives you the string that matters in this case. Does it have more than 255 characters?

 

[rachexcel]

Ahh I see. Sorry!

This is what it produces:

=AVERAGE(IF(MONTH('[Monthly RH Decision report -Automating.xlsm]Data'!$C$2:$C$2315)=5,IF('[Monthly RH Decision report -Automating.xlsm]Data'!R2C5:R2315C5=Y,'[Monthly RH Decision report -Automating.xlsm]Data'!$X$2:$X$2315))

I've done a character count on this and it's 210 characters (no spaces) and still only 222 with spaces.

Are there other reasons for this error?

Thanks,
Rach

 

[pgc01]

Hi

Never mix in the same formula the A1 and R1C1 notations! that's not allowed. Choose one of them.

For ex., if you choose R1C1 notation:

Range("A1").FormulaArray = "=AVERAGE(IF(MONTH('[Monthly RH Decision report -Automating.xlsm]Data'!R2C3:R2315C3)=5,IF('[Monthly RH Decision report -Automating.xlsm]Data'!R2C5:R2315C5=Y,'[Monthly RH Decision report -Automating.xlsm]Data'!R2C24:R2315C24)))"

Remarks:
1 - you were also missing one closing parenthesis (closing 1 Average() and 2 If()'s)
2 - make sure the Monthly RH Decision report -Automating.xlsm file exists
3 - Is Y (in the formula) a name? else you must enclose it in double quotes (in this case doubled)

 

[rachexcel]

You're brilliant! Thank you.

 

[pgc01]

You're welcome. Thanks for the feedback.