PDA

View Full Version : Calculate Percent Error



NWE
04-09-2019, 10:26 AM
Hello All,

I am attempting to calculate a percent error with this code and I am not getting the right result. So the value in "A4" is -53, and should return a percent error of 75 percent. Any thoughts?


Sub CalculatePercentError()
'Calculate the errors in cells
Worksheets("Generalized Report").Activate
If Worksheets("Generalized Report").range("A4").Value = 0 Then 'Division by 0 error will occur
Worksheets("Generalized Report").range("A6").Value = Null
Else
Worksheets("Generalized Report").range("A6").Value = Abs(range("A4").Value \ (WorksheetFunction.Sum(Worksheets("Contract").range("H2:H10000")))) * 100
End If
End Sub

Bob Phillips
04-09-2019, 10:35 AM
Try this


Sub CalculatePercentError()
'Calculate the errors in cells

With Worksheets("Generalized Report")

If .Range("A4").Value = 0 Then 'Division by 0 error will occur

.Range("A6").Value = Null
Else

.Range("A6").Value = Abs(.Range("A4").Value \ Application.Sum(Worksheets("Contract").Range("H2:H10000")))
.Range("A6").NumberFormat = "0.0%"
End If
End With
End Sub

NWE
04-09-2019, 10:43 AM
Worked like a charm, Thanks!

Bob Phillips
04-09-2019, 10:46 AM
As it worked, for your info, the only 'real' change I made was to make sure that Range("A4") referred to the Generalized workbook, your code left it referring to whatever was the activeworkbook, the rest was just cosmetic/better coding practices/my coding style.

NWE
04-09-2019, 11:02 AM
Yes rereading my code I just caught that. But if I say "Acivate X workbook" wouldn't the code assume everything below was referring to said workbook?