AverageIF errors when formula is created from another sheet

[xbricx]

In Sheet1 I have a button that creates in Sheet2 a calculation in cell C10 that I want to be =AverageIF($C$8:$C$9; "<>0"). Pretty easy, isn’t it ?

Well naaaahhh … after many hours googling and testing different approaches, I’m still unable to make it work. Here are my attempts and the error messages associated with it.

‘ First, here are the defined variables
Dim FormulaText as String ‘ I gradually prepare the formula in strings before sending it to the cell : usefull when I have very complex formula
Dim MSN as WorkSheet ‘ that’s the target worksheet where the formula will be sent
Dim row, col as Integer ‘ yep … exactly what you think it is …


‘Now let’s start with the code
row = 10
col = 3

‘ Step 1 : Build the range text to see if that part work
FormulaText = MNS.Cells(row - 2, col).Address & ":" & MNS.Cells(row - 1, col).Address ' at this point FormulaText returns $C$8:$C$9 which is what I expect to get

‘ Step 2 : Build the formula itself
FormulaText = "AverageIF(" & FormulaText & "; ""<>0"")" 'here FormulaText returns AverageIF($C$8:$C$9; "<>0") which is again what I expect to get

‘ Step 3 : Test that formula to the target cell without the = to see if the string is still right
MNS.Range(Cells(10, col).Address).Value = FormulaText ‘ which returns the string AverageIF($C$8:$C$9; "<>0") in C10 as expected.

‘So far so good. And if I manually add the missing = into Sheet2 C10 cell, the formula becomes =AverageIF($C$8:$C$9; "<>0") and it does exactly what I expect it to do

‘Now let’s go back and try to make it work with code from Sheet1

‘ When in Step 2 I build the formula to add the missing =, FormulaText returns =AverageIF($C$8:$C$9; "<>0") which is again what I expect to get (tested with msgbox)

‘but when I send it to C10 my problems begin

MNS.Range(Cells(10, col).Address).Value = FormulaText ‘ returns Application-defined or Object-defined error
MNS.Range(Cells(10, col).Address).Formula = FormulaText ‘ returns same error Application-defined or Object-defined error
MNS.Range(Cells(10, col).Address).FormulaArray = FormulaText ‘ returns Unable to set the FormulaArray property of the Range class

‘ ok, maybe I need to add curled brackets to the formula so let’s say I have this line just after Step 2
FormulaText = "={" & FormulaText & "}" ‘ it returns ={AverageIF($C$8:$C$9; "<>0")} as expected

‘ but when I send it to C10, nothing change, same set of error messages as below.


‘ Now when I make that formula a little bit simpler or far more complex (!!!), it works :

‘ If in Step2 I go for the Average formula instead
FormulaText = "=Average(" & FormulaText & ")" ‘ it returns =AVERAGE($C$8:$C$9) as expected

' and Step3
MNS.Range(Cells(10, col).Address).Value = FormulaText ‘ works perfectly well (same with .Formula and .FormulaArray)

‘ I also used the same recipe in a far more complex formula and similar context (I mean a button in Sheet1 creates the formula for Sheet2)

‘ Step 2 was
FormulaText = "RANK(R" & row - 2 & "C" & col & "," & Chr(39) & MNS.Name & Chr(39) & "!R" & row - 2 & "C3:R" & row - 2 & "C" & StudentNumber & ")"
FormulaText = "=IF(R" & row - 2 & "C" & col & "<>0," & FormulaText & ","""")"
‘ which in the end returned something nice like =IF($C$28<>0;RANK($C$28;'TPGr01'!$C$28:$U$28);"") and that too worked well.


So, what did I miss with my AverageIf formula ? Thanks in advance for all ideas/hints/solutions/inspirations.

 

[xbricx]

Thanks Fluff.

I didn't know about the # icon and about the delay. It is indeed far easier to read as you modified it.

I've tried the regional settings as you suggested and unfortunately it doesn't work either. I've checked again and I see the the first round of TextFormla returns $A$1:$A$2, so the regional setting are been taken care by the code. What is really sent is then =AVERAGEIF($A$1:$A$2, "<>0").

I've modified the code a bit and gave more explicit explanations in my comments and I post it here under so I would be easier for someone to test it (also a good occasion to test the # icon) :

Option Explicit

Sub TestAverageIf()
   ' this sub is located under Sheet1 
   ' and is called from a button on the Sheet1 page
   ' and the result is shown in Sheet2

   Dim TextFormula As String
   Dim ws As Worksheet
   Dim MNS As Worksheet
   Dim ResultPage As String

   On Error GoTo Errorcatch

   ' set the sheet where I want the output to appear 
   ResultPage = "Sheet2"
   For Each ws In Worksheets
      If ws.CodeName = ResultPage Then
         Set MNS = ws
         MNS.Activate
         MNS.UsedRange.Clear    ' remove stuff from previous tests
         Exit For
      End If
   Next ws

   ' put some values in A1 and A2 to later see if the formula works
   MNS.Range(Cells(1, 1).Address).Value = 1
   MNS.Range(Cells(2, 1).Address).Value = 2 

   ' start building the formula
   TextFormula = MNS.Cells(1, 1).Address & ":" & MNS.Cells(2, 1).Address     ' at this point TextFormula is $A$1:$A$2
   TextFormula = "=AverageIF(" & TextFormula & "; ""<>0"")"                       ' at this point TextFormula is =AverageIF($A$1:$A$2; "<>0")

   ' Test 1 : send TextFormula in A3 
   MNS.Range(Cells(3, 1).Address).Formula = TextFormula                              ' returns "Application-defined or Object-Defined error"

   ' Test 2 : explicitely send the formula into A3 
   'MNS.Range(Cells(3, 1).Address).Formula = "=AverageIf($A$1:$A$2; ""<>0"")"        '  same as previous ... doesn't work

   ' Test 3 : see if the formula works with Avarage
   'MNS.Range(Cells(3, 1).Address).Formula = "=Average(A1:A2)"                          ' it works .... but it is not AverageIf


   Errorcatch:
   If Err.Description <> "" Then
      MsgBox Err.Description
   End If

End Sub

 

[Fluff]

Try entering the formula manually into a cell, What happens?

 

[xbricx]

Try entering the formula manually into a cell, What happens?

The strange thing is "it works" if done manually.

Let's say I do this :

[FONT=Verdana]TextFormula = MNS.Cells(1, 1).Address &":" & MNS.Cells(2, 1).Address [/FONT][FONT=Verdana]
TextFormula = "AverageIF(" & TextFormula &"; ""<>0"")"                       ' = is removed just to get the string

MNS.Range(Cells(3, 1).Address).Value = TextFormula                              '  the string [/FONT][FONT=Verdana]AverageIF($A$1:$A$2; "<>0") appears in A3[/FONT]

Now, after the code has been run, if I go into Sheet2 and manually add the missing = in A3, the formula works.

I've tried to do it in 2 passes : first sending the string as described above and then modify that string through code to add the missing =.

MNS.Range(Cells(3, 1).Address).Value = TextFormula                                     ' no problem here : [FONT=Verdana]AverageIF($A$1:$A$2; [/FONT][FONT=Verdana]"<>0") [/FONT][FONT=Verdana]appears in A3[/FONT][FONT=Verdana]<>0") 

[/FONT]MNS.Range(Cells(3, 1).Address).Formula = "=" & MNS.Range(Cells(3, 1).Address).Value   ' that '[FONT=Verdana]Application-defined or Object-Defined error" again [/FONT]
[FONT=Verdana]<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->[/FONT]

 

[Fluff]

If the formula works when added manually, then I cannot understand why it won't work when done by VBA

 

[xbricx]

BINGO !!!

I've finally found the solution:

MNS.Range(Cells(3, 1).Address).FormulaR1C1 = "=AVERAGEIF(R[-2]C:R[-1]C, ""<> 0"")"

And that kind of cell reference also work with SumIf and CountIf when the goal is to send a formula from a sheet to another one.

Thanks again to all who gave feedbacks and ideas.

 

[Fluff]

Glad you got it sorted.