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.

 

[Dryver14]

Could it be that you have Dim MSN as Worksheet and are using MNS in the code?

 

[xbricx]

Could it be that you have Dim MSN as Worksheet and are using MNS in the code?

Thanks Dryver14.

Although I've made the DIM MSN mistake as you rightly pointed it, in my real excel code it is DIM MNS.
I should have Cut&Pasted that section instead of typing it for this post.

 

[xbricx]

An unfortunately, it looks like I cannot edit this mistake in my original post.

So, it should read Dim MNS as WorkSheet and everything else under it still apply.

 

[Dryver14]

I still think it has something to do with the worksheet but without the code I don't know

add worksheet, Name worksheet, set MNS = worksheet.name

 

[xbricx]

Thanks for looking at it. I know it is not easy to read to someone else code

I've add set MNS = Sheet1.Name just before Step1 but I got a Type mismatch error.

In fact, the MNS worksheet is set earlier in my code (not shown in my post ... it is very very long code as I rebuild a page line by line)

' here is where I set MSN at the beginning of the code
' where I pass PageResultats parameter as the Sheet2 name and where I also reset Sheet2 as a blank page
For Each ws In Worksheets
If ws.CodeName = PageResultats Then
Set MNS = ws
For Each Shp In ws.Shapes
Shp.Delete
Next Shp
MNS.Activate
MNS.UsedRange.Clear
Exit For
End If
Next ws

After that, I successfully use MNS to rebuild Sheet2 line after line with code that looks like this (just as an example) :

' line 5
With MNS.Range(Cells(5, col).Address)
.Formula = "='" & wsListeEtudiants.Name & "'!D" & NoEtudiant + 2
End With

All rebuilt lines work well until I reach the one with AverageIf.

 

[xbricx]

Ooops ...
I mean I've add set MNS = Sheet2.Name just before Step1 but I got a Type mismatch error.

 

[xbricx]

Still a complete mistery for me: I'm still trying to send a formula like =AverageIf(A1:A2; "<>0") into another sheet but I got no progress.

To test the problem, I've created a blank workbook and started from scratch with just minimal code so anyone could easily reproduce the problem.

In Sheet1, there is only a button calling Sub TestAverageIf. This TestAverageIf code is under Sheet1.
There is nothing else in that workbook.

So, unless I miss something very basic, anyone running the following code should get "Application-defined or Object-Defined error".

But why ? And how could I get it to work ?

----

Option Explicit


Sub TestAverageIf()
   ' called from a button in Sheet1


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


   ResultPage = "Sheet2"       ' where I want the output to appear 


   On Error GoTo Errorcatch


   For Each ws In Worksheets
       If ws.CodeName = ResultPage Then
           Set MNS = ws
           MNS.Activate
           MNS.UsedRange.Clear
           Exit For
       End If
   Next ws


   MNS.Range(Cells(1, 1).Address).Value = 1   ' put value 1 in A1 just for testing 
   MNS.Range(Cells(2, 1).Address).Value = 2   ' put  2 in A2  


   TextFormula = MNS.Cells(1, 1).Address & ":" & MNS.Cells(2, 1).Address  ' at this point TextFormula is A1:A2
   TextFormula = "=AverageIF(" & TextFormula & "; ""<>0"")"                    ' at this point TextFormula is =AverageIF(A1:A2; "<>0")


   MNS.Range(Cells(3, 1).Address).Formula = TextFormula                          ' returns "Application-defined or Object-Defined error"
   'MNS.Range(Cells(3, 1).Address).Value = "=AverageIf(A1:A2; ""<>0"")"   ' more explicit than TextFormula but same as previous ... doesn't work
   'MNS.Range(Cells(3, 1).Address).Value = "=Average(A1:A2)"                   ' but Average without if works


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


End Sub

 

[Fluff]

@xbricx
When posting code please use code tags, the # icon in the reply window.

Also please do not make duplicate posts.
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.

As to your problem, depending on you regional settings, should the formula be
=AVERAGEIF($A$1:$A$2, "<>0")

 

[Fluff]

Crossposted https://www.excelforum.com/excel-pr...ageif-formula-created-from-another-sheet.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.