Need help with VBA Macro Run-time error 6

[htran1984]

Hello all, coming to experts for some assistance. I'm having issue running a macro that was created by a coworker who's still out on furlough. I'm a total noob when it comes to vba and having a tough time trying to resolve issue.

I am getting the error Run-time Error '6': Overflow for this part of the loop in macro and where the error occurs - see bold yellow. The macro runs fine when there's data/value. I am assuming it can't calculate the percentage with zeros. How do I go about correcting formula to list as zero percent when there's no data/value?

I'm not even sure if that's right approach to resolving issue. Appreciate any guidance you can provide.

'Loop for adding in Rank and running totals
Range("A2").Select
Do
'Loop to check if month & YTD is 0
Do
If (Application.WorksheetFunction.Sum(ActiveCell.Offset(0, 4), ActiveCell.Offset(0, 7)) = 0) And _
(Not (ActiveCell.Value = Empty)) Then
Rows(ActiveCell.Row).EntireRow.Delete
Else
End If
Loop Until (ActiveCell.Value = Empty) Or (Application.WorksheetFunction.Sum(ActiveCell.Offset(0, 4), ActiveCell.Offset(0, 7)) > 0)
'Insert Rank #
If Not (ActiveCell.Value = Empty) Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Row - 1
If Right(ActiveCell.Offset(0, 1).Value, 1) = "0" Then
'Underline row
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 8)).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
'Formula for # for up to this set of 10
ActiveCell.Offset(0, 5).Value = Format(Application.WorksheetFunction.Sum( _
Range(Cells(2, 5), Cells(ActiveCell.Row, 5))), "#,##0")
'Formula for % for up to this set of 10
ActiveCell.Offset(0, 6).Value = Format((ActiveCell.Offset(0, 5).Value) / _
(Application.WorksheetFunction.Sum(Range("E:E"))), "0%")
Else
End If
ActiveCell.Offset(1, 0).Activate
End If
Loop Until ActiveCell.Value = Empty

Requestor Portal	Ranking	Requestor First Name	Requestor Last Name	Total Tickets Requested	Tickets Running Total	% of Total Tickets	2020 Calendar Year-to-date (run 2021-01-27)
User 1	1​	User 1	User 1	0​			17,022​
User 2	2​	User 2	User 2	0​			13,706​
User 3	3​	User 3	User 3	0​			6,641​
User 4	4​	User 4	User 4	0​			3,705​
User 5	5​	User 5	User 5	0​			3,182​
User 6	6​	User 6	User 6	0​			2,513​
User 7	7​	User 7	User 7	0​			2,461​
User 8	8​	User 8	User 8	0​			1,109​
User 9	9​	User 9	User 9	0​			1,059​
User 10	10​	User 10	User 10	0​	0​		1,055​

 

[mart37]

If Application.WorksheetFunction.Sum(Range("E:E")) <> 0 then ActiveCell.Offset(0, 6).Value = Format((ActiveCell.Offset(0, 5).Value) / _
(Application.WorksheetFunction.Sum(Range("E:E"))), "0%")

 

[htran1984]

Thank you so much, you're a life saver! That formula resolved the issue.

Else where in the macro, I'm running into the same issue dividing by zero. I think I can apply the If Then statement correct?

'Average Requests per Requestor
Range("G8").Value = Range("G6").Value / Range("G5").Value

Replace with:
If Range("G5").Value <> 0 then Range("G8").Value = Range("G6").Value / Range("G5").Value