Overflow Error Trying to Calculate Percentages

[cwojtak]

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!

[SamT]

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]

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]

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]

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]

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]

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]

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]

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

[snb]

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