Need help with VBA Macro Run-time error 6

htran1984

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
RankingRequestor
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 1User 1
0​
17,022​
User 2
2​
User 2User 2
0​
13,706​
User 3
3​
User 3User 3
0​
6,641​
User 4
4​
User 4User 4
0​
3,705​
User 5
5​
User 5User 5
0​
3,182​
User 6
6​
User 6User 6
0​
2,513​
User 7
7​
User 7User 7
0​
2,461​
User 8
8​
User 8User 8
0​
1,109​
User 9
9​
User 9User 9
0​
1,059​
User 10
10​
User 10User 10
0​
0​
1,055​
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
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%")
 
Solution

htran1984

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,838
Messages
5,627,181
Members
416,227
Latest member
Abid Fattani

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top