AverageIf formula created from another sheet

Hi,

I've created a sub that dynamically prepare a new sheet based on multiple criterias. At one point in the preparation of that new sheet, if need to average the values of some cells for which the content is not 0. As far as I know, AverageIf is the formula to use. The problem is I get an error each time I try to send that formula to the new sheet.

I've tried several ways around (using curled brackets around the formula, using .formulaarray instead of .formula) but I got errors each time. From what I've found in other thread, I know I could use some way around that looks like "sum cell1 + cell2 if they are not 0 and divide that sum by the count of cells that are not 0".

I may have to use that solution but I still wonder what is not correct with my logic when I use the AverageIf. 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. The code for TestAverageIf (here under) is located 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 ? Can someone tell me what I'm missing in my logic ? I need to better understand it because I will use a lot the same logic elsewhere in my project.

Your help is really appreciated.

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:$A2; "<>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

This question was also posted 3 days ago on
https://www.mrexcel.com/forum/excel-...r-sheet-2.html

Rule 08: Cross-posting Without Links

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

No further help to be offered, please, until the OP has complied with this request.

Thank you for adding the cross posting information.
To mark the thread as 'Solved' use the thread tools link above your first post.