SliderJeff
Board Regular
- Joined
- Oct 5, 2016
- Messages
- 63
Hey gang,
So I have added a bunch of error handling code for when the AverageIf worksheet function is called, but it seems to be missing when the #DIV/0 error happens.
Basically what I have is a bunch of non-zero numbers in Column D, followed by a bunch of zero values in Column E. The code I am executing is as follows:
For the first call to AverageIf above, outputVal has a value of 18.976. However, whereas I expect outputVal to become an error of "#DIV/0" on the second call to the AverageIf function, it just keeps it's previous value of 18.976 and carries on with the next line of code, completely skipping the line
almost as if it didn't even attempt to evaluate the new AverageIf call for outputVal.
Can someone explain what is going on and why my outputVal variable is not correctly being calculated for the second call to AverageIf?
Thanks,
Jeff
So I have added a bunch of error handling code for when the AverageIf worksheet function is called, but it seems to be missing when the #DIV/0 error happens.
Basically what I have is a bunch of non-zero numbers in Column D, followed by a bunch of zero values in Column E. The code I am executing is as follows:
Code:
' Find Average Temperature
On Error Resume Next
outputVal = Application.WorksheetFunction.Average(inputWorkbook.Worksheets(fileName).Columns("D:D"))
On Error GoTo 0
If IsEmpty(outputVal) Then
outputVal = 0
End If
outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("TEMPERATURE_COL")).Value = outputVal
' outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("TEMPERATURE_COL")).Value = Application.WorksheetFunction.Average(inputWorkbook.Worksheets(fileName).Columns("D:D"))
outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("TEMPERATURE_COL")).NumberFormat = "0"
' Find Average of non-zero Lateral Shocks
On Error Resume Next
outputVal = Application.WorksheetFunction.AverageIf(inputWorkbook.Worksheets(fileName).Columns("E:E"), ">0")
On Error GoTo 0
If IsEmpty(outputVal) Or IsError(outputVal) Then
outputVal = 0
End If
outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("LATERAL_SHOCK_COL")).Value = outputVal
For the first call to AverageIf above, outputVal has a value of 18.976. However, whereas I expect outputVal to become an error of "#DIV/0" on the second call to the AverageIf function, it just keeps it's previous value of 18.976 and carries on with the next line of code, completely skipping the line
Code:
If IsEmpty(outputVal) Or IsError(outputVal) Then
almost as if it didn't even attempt to evaluate the new AverageIf call for outputVal.
Can someone explain what is going on and why my outputVal variable is not correctly being calculated for the second call to AverageIf?
Thanks,
Jeff