AverageIF and IFError VBA macro cell formula

[BN2Wild]

I have written an intense macro, that averages a row at a time from a beginning cell to an end cell.

The following line works fine:

Please Login or Register  to view this content.

However, I am now trying to change this to average only cells with non-zeros.

As many have experienced, when you have a total row with all zeros, it will bug out to only use AverageIF statement, so I was combining IfError statement to combat that.
I don't think there is an error in the statement, so there must be a rule that this doesn't fit or work within the confines of the rules in VBA.

But if typed directly in the cell it works. Recorded a R1C1 macro of this to see if I can convert it to VBA variables:
Recorded:

Please Login or Register  to view this content.

Trying to input (but VBA tells me error):

Please Login or Register  to view this content.

ANY IDEAS??? thanks in advance!

[TMS]

Try:

Please Login or Register  to view this content.

[AliGW]

Administrative Note:

Welcome to the forum.

Is your forum profile showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[BN2Wild]

Try:

Please Login or Register  to view this content.

Unfortunately, this does NOT work as it doesn't have the AVERAGEIF statement in it, so it averages all cells together whether it's a zero or not, so having say 5 zeros plus a number will divide by a total of 6.
I did try a varient of this, but it always bugs out using the AVERAGEIF where the commas show up...

Please Login or Register  to view this content.

Is AverageIf not allowed in VBA for an Active Cell statement?

[BN2Wild]

Here is a visual picture of the issue, and it does work in the worksheet, formula is correct for J11, however, I can't seem to get the syntax correct in the VBA:
Attachment 878959

[TMS]

Post the formula as it is, working, in the spreadsheet. Then I/we can see what it should look like.

Post a workbook, not pictures.

[BN2Wild]

Post the formula as it is, working, in the spreadsheet. Then I/we can see what it should look like.

Post a workbook, not pictures.

Sorry about that, I had posted it prior, but was demonstrating that it didn't work...

Regardless, I solved the issue myself with the following, It was just syntax and sharing in case someone else has the same issues, it might help them...

Please Login or Register  to view this content.