Excel VBA unable to get the AverageIf, Run-time error 1004

[EuginG]

Hi!

Please help me to solve a problem.

I have Excel’s reports created by some analytic software. There are many data there.
One of my actions is to apply AverageIf function for two columns (average from L column based on G column criteria).
Moreover L column numbers originally go as a text (e.g. 15). And I need to convert it to countable numbers first (e.g. 15,00).

It is Ok! Looks like I’ve found a solution for conversion.

But the problem is that if the Excel’s report does not have at least one of seven values (in G column)that I am searching for, script goes to Error (Debug) and do not work properly. And I need to delete manually the strings which contain unnecessary values. Then Run script again. Because report’s data may vary (hundreds of rows), it becomes a problem to my script.

How can I avoid the Error and force strings to skipping, if they are unnecessary?

Sub test()

Dim Cell As Range, gRange As Range, lRange As Range

Set gRange = Range("G4:G1000")
Set lRange = Range("L4:L1000")

lRange.Select

With Selection
Selection.NumberFormat = "0.00"
.Value = .Value
End With

Range("M2:T2").EntireColumn.NumberFormat = "#.00"

Range("M2").Value = Application.WorksheetFunction.AverageIf(gRange, 2, lRange)
Range("O2").Value = Application.WorksheetFunction.AverageIf(gRange, 3, lRange)
Range("N2").Value = Application.WorksheetFunction.AverageIf(gRange, 2.1, lRange)
Range("P2").Value = Application.WorksheetFunction.AverageIf(gRange, 11, lRange)
Range("R2").Value = Application.WorksheetFunction.AverageIf(gRange, 19, lRange)
Range("S2").Value = Application.WorksheetFunction.AverageIf(gRange, 21, lRange)
Range("T2").Value = Application.WorksheetFunction.AverageIf(gRange, 33, lRange)


End Sub

 

[Domenic]

Use the AverageIf method of the Application object instead. This way the error won't cause your code to break, and a #DIV/0! error will be entered in the cell...

Range("M2").Value = Application.AverageIf(gRange, 2, lRange)

Hope this helps!

 

[EuginG]

Use the AverageIf method of the Application object instead. This way the error won't cause your code to break, and a #DIV/0! error will be entered in the cell...

Range("M2").Value = Application.AverageIf(gRange, 2, lRange)

Hope this helps!

It helped! Thank you, Domenic! So simple..

 

[Domenic]

Glad I could help.

Cheers!