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
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