View Full Version : [SOLVED:] Overflow Error Trying to Calculate Percentages
cwojtak
02-16-2022, 02:59 PM
I'm doing some math within a large if statement as shown below to see if the results are within 6% however when I bring it to large data groups I get an 'OverFlow' error.. I assume because the decimal points go on too long. Any solution advice is appreciated! My guess is I'll need to do this using variables but I have about 5 of these throughout the entire sub so I'd prefer not to create and keep track of that many variables..
Do Until i = Sh3EndRow + 1
If Sh3.Range("CI" & i).Value = "Yes" Then
Sh3.Range("CJ" & i).Value = "No"
Else
'Order Columns N-S
'Deals Reference BB-BG
'Final Consensus Plan BV-CA
If (Sh3.Range("BB" & i).Value + Sh3.Range("N" & i).Value) / Sh3.Range("BV" & i).Value < 0.94 Or _
(Sh3.Range("BC" & i).Value + Sh3.Range("O" & i).Value) / Sh3.Range("BW" & i).Value < 0.94 Or _
(Sh3.Range("BD" & i).Value + Sh3.Range("P" & i).Value) / Sh3.Range("BX" & i).Value < 0.94 Or _
(Sh3.Range("BE" & i).Value + Sh3.Range("Q" & i).Value) / Sh3.Range("BY" & i).Value < 0.94 Or _
(Sh3.Range("BF" & i).Value + Sh3.Range("R" & i).Value) / Sh3.Range("BZ" & i).Value < 0.94 Or _
(Sh3.Range("BG" & i).Value + Sh3.Range("S" & i).Value) / Sh3.Range("CA" & i).Value < 0.94 Then
Sh3.Range("CJ" & i).Value = "Check"
Else
Sh3.Range("CJ" & i).Value = "Delete"
End If
End If
i = i + 1
Loop
Thank you in advance!
Decimals are limited to 15 places by the OS. I think your error is too long of an IF()
Dim BBNBV As Boolean, BDPBW As Boolean, *abcde* As Boolean,,,,etc
If BlahBlah 'CI = "Yes"
Blah
Else
With Sh3.Rows(i) 'Only need to reference the .Cells by column; The "." refers to Sh3.Rows(i)
BBNBV = (.Cells("BB") + .Cells("N")) / .Cells("BV)) <0.94
BCOBW = (.Cells("BB") + .Cells("N")) / .Cells("BP") < 0.94
Etc
'Add all Booleans: "+" is Math for Boolean "OR"; "*" is math for boolean "AND"
If CBOOL(BBNBV + BCOBW + *abcde* +ect) Then 'Convert the math back to Boolean
.cells("CJ").Value = "Check"
Else
.Cells("CJ").Value = "Delete"
End If 'Adding Booleans
End With 'Rows(i)
End If 'CI = Yes
BlahBlah
Paul_Hossler
02-16-2022, 04:05 PM
Without a sample workbook with some sample data, this is only a guess
Sub Test()
'Order Columns N-S
'Deals Reference BB-BG
'Final Consensus Plan BV-CA
Const Orders = 14 ' col N
Const Deals = 54 ' col BB
Const Final = 74 ' col BV
Dim r As Long, c As Long
With sh3
Do Until r = Sh3EndRow + 1
With .Rows(r)
If .Cells(87).Value = "Yes" Then
.Cells(88).Value = "No"
Else
.Cells(88).Value = "Check"
For c = 0 To 5
If ((.Cells(Deals + c).Value + .Cells(Orders + c).Value) / .Cells(Final + c).Value) < 0.94 Then GoTo NextRow
Next c
.Cells(88).Value = "Delete"
End If
End With
NextRow:
r = r + 1
Loop
End With
End Sub
cwojtak
02-17-2022, 11:38 AM
I like this strategy! I'm getting a runtime error 5 when I try to set the first Boolean = to true or false based on my equation though.. Any idea what could be causing it? Updated code is below.
If Range("CI" & i).Value = "Yes" Then
Sh3.Range("CJ" & i).Value = "No"
Else
With Sh3.Rows(i) 'Only need to reference the .Cells by column; The "." refers to Sh3.Rows(i)
BBNBV = (.Cells("BB") + .Cells("N")) / .Cells("BV") < 0.94 '**Run Time Error 5, invalid procedure or call argument
BCOBW = (.Cells("BC") + .Cells("O")) / .Cells("BW") < 0.94
BDPBX = (.Cells("BD") + .Cells("P")) / .Cells("BX") < 0.94
BEQBY = (.Cells("BE") + .Cells("Q")) / .Cells("BY") < 0.94
BFRBZ = (.Cells("BF") + .Cells("R")) / .Cells("BZ") < 0.94
BGSCA = (.Cells("BG") + .Cells("S")) / .Cells("CA") < 0.94
'Add all Booleans: "+" is Math for Boolean "OR"; "*" is math for boolean "AND"
If CBool(BBNBV + BCOBW + BDPBX + BEQBY + BFRBZ + BGSCA) Then 'Convert the math back to Boolean
.Cells("CJ").Value = "Check"
Else
.Cells("CJ").Value = "Delete"
End If 'Adding Booleans
End With 'Rows(i)
End If 'CI = Yes
Paul_Hossler
02-17-2022, 11:48 AM
Yes - use the column number in the .Cells( ) like I did
Wasteful of cycles
1. If first BBNVB fails, no need to do the rest
2. I think the If Range("CI ... can be inside the With Sh3.Rows(i)
3. Why mess around with converting Booleans to math?
cwojtak
02-17-2022, 12:52 PM
Wow!! The then GoTo logic is genius! I was banging my head over how your solution worked so I started with the other and was going to look at yours again after.
Thank you for the help! Implementing it for my other ifs and will revert back on if it solved the issue!
Paul_Hossler
02-17-2022, 01:19 PM
A lot of people will tell you to never use GoTo since it can lead to spaghetti code
As a rule, IMHO that's true, but like all rules it's made to be broken
I've found that the code is more readable if GOTo's are CAREFULLY used
Silly made up example.
Variable names are very short and the conditional tests are very easy, but if they were longer and/or more complicated, I (again personal style) find the code easier to follow and debug
I don't think VBA has shortcut evaluation so something like "(cond1) And (cond2) And (cond3) Then" would require all 3 conditions to be evaluated all the time event if cond1 = False
You can 'tune' #3 by putting the condition most likely to = False first, so that most of the time the rest are not evaluated
Option Explicit
Sub One()
'#1 lots of nested Ifs
For i = 1 To 10
If A(i) = 100 Then
If B(i) = 200 Then
If C(i) = 300 Then
D(i) = 400
Else
D(i) = 500
End If
End If
End If
Next i
'#2 And-ed
For i = 1 To 10
If A(i) = 100 And B(i) = 200 And C(i) = 300 Then
D(i) = 400
Else
D(i) = 500
End If
Next i
'#3 GoTo
For i = 1 To 10
D(i) = 500
If A(i) <> 100 Then GoTo NextI
If B(i) <> 200 Then GoTo NextI
If C(i) <> 300 Then GoTo NextI
D(i) = 400
NextI:
Next i
End Sub
cwojtak
02-17-2022, 04:12 PM
I completely agree! Nested Ifs can get extremely hard to read. I'll start implementing that strategy when I can going forward!
So I've updated my code and I think I've found the issue. The error is occurring when Deals are blank, Orders are 0 and Final is also 0. Since in my end solution, if those are all blank I won't be doing anything with them. I've tried to add this so if deals, orders and final are all blank or 0 it will delete the row. When I run it though nothing happens.. I've looked at it for about an hour now and just can't figure out what I'm missing..
Const Orders = 14 ' col N
Const Deals = 54 ' col BB
Const Final = 74 ' col BV
Dim r As Long, c As Long
r = 8
i = 8
With Sh3
Do Until i = Sh3EndRow + 1
With .Rows(r)
For c = 0 To 5
If Not IsEmpty(.Cells(Final + c).Value) Or Not .Cells(Final + c).Value = 0 Then GoTo NextRow 'Final is most likely to have a value
If Not IsEmpty(.Cells(Orders + c).Value) Or Not .Cells(Orders + c).Value = 0 Then GoTo NextRow 'Orders are second most likely to have a value
If Not IsEmpty(.Cells(Deals + c).Value) Or Not .Cells(Deals + c).Value = 0 Then GoTo NextRow 'If the above two are true, I bet this will be as well
Next c
.EntireRow.Delete xlShiftUp
r = r - 1
End With
NextRow:
r = r + 1
i = i + 1
Loop
End With
Paul_Hossler
02-17-2022, 07:38 PM
I'd do it in two steps like below since Deletes should be bottoms to top
Option Explicit
Sub Test2()
'Order Columns N-S
'Deals Reference BB-BG
'Final Consensus Plan BV-CA
Const Orders = 14 ' col N
Const Deals = 54 ' col BB
Const Final = 74 ' col BV
Dim r As Long, c As Long
Dim sh3StartRow As Long, sh3EndRow As Long
With sh3
sh3StartRow = 2 ' <<<<<<<<<<<<<<<<<<<<<<<<<<<
sh3EndRow = .Cells(sh3StartRow, 88).End(xlDown).Row ' ?????????????????????????????
'------------------------------------------------------------------------------------------
'step 1 mark status
r = sh3StartRow
Do Until r = sh3EndRow + 1
With .Rows(r)
If .Cells(87).Value = "Yes" Then
.Cells(88).Value = "No"
Else
.Cells(88).Value = "Check"
For c = 0 To 5
If .Cells(Deals + c).Value = 0# Then Exit For ' leave c loop but (88) will = "Delete"
If .Cells(Orders + c).Value = 0# Then Exit For
If .Cells(Final + c).Value = 0# Then Exit For
If ((.Cells(Deals + c).Value + .Cells(Orders + c).Value) / .Cells(Final + c).Value) < 0.94 Then GoTo NextRow
Next c
.Cells(88).Value = "Delete"
End If
End With
NextRow:
r = r + 1
Loop
'------------------------------------------------------------------------------------------
'step 2 delete rows with "Delete" in (88) - Deletes should be bottoms to top
For r = sh3EndRrow To sh3StartRow Step -1 ' you never said what your start row was in above
If .Cells(r, 88).Value = "Delete" Then .Rows(r).Delete
Next r
End With
End Sub
Another approach that MIGHT work combines the 2 steps but goes bottom-up
Option Explicit
Sub Test2()
'Order Columns N-S
'Deals Reference BB-BG
'Final Consensus Plan BV-CA
Const Orders = 14 ' col N
Const Deals = 54 ' col BB
Const Final = 74 ' col BV
Dim r As Long, c As Long
Dim sh3StartRow As Long, sh3EndRow As Long
With sh3
sh3StartRow = 2 ' <<<<<<<<<<<<<<<<<<<<<<<<<<<
sh3EndRow = .Cells(sh3StartRow, 88).End(xlDown).Row ' ?????????????????????????????
'------------------------------------------------------------------------------------------
For r = sh3EndRow To sh3StartRow Step -1
If .Cells(r, 87).Value = "Yes" Then
.Cells(r, 88).Value = "No"
Else
.Cells(r, 88).Value = "Check"
For c = 0 To 5
If .Cells(r, Deals + c).Value = 0# Then Exit For ' leave the c loop but row r will be deleted
If .Cells(r, Orders + c).Value = 0# Then Exit For
If .Cells(r, Final + c).Value = 0# Then Exit For
' get the next row (Step -1) leaving (88) = "Check"
If ((.Cells(r, Deals + c).Value + .Cells(r, Orders + c).Value) / .Cells(r, Final + c).Value) < 0.94 Then GoTo NextRow
Next c
.Rows(r).Delete
End If
NextRow:
Next r
End With
End Sub
You could consider to use VBA:
Sub M_snb()
sn = sh3.UsedRange.Resize(, 88)
For j = 1 To UBound(sn)
If sn(j, 87) = "Yes" Then
sn(j, 88) = "No"
Else
y = 0
For jj = 0 To 5
y = y - ((sn(j, 54 + jj) + sn(j, 14 + jj)) / sn(j, 74 + jj) < 0.94)
Next
sn(j, 88) = IIf(y = 0, "Delete", "Check")
End If
Next
sh3.UsedRange.Resize(, 88) = sn
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.