VBA Overflow Error

[Niraj Baraili]

Any feedback will be much appreciated why i am getting Overflow Error in below code:

    Dim SrchRng2 As Range, SrchRng1 As Range, cel As Range, cel1 As Range
    Dim i As Long
    Dim j As Long
      
    lastrow1 = wkb2.Sheets("Summary").Cells(Rows.Count, "OE").End(xlUp).Row
    
    Set SrchRng2 = wkb2.Sheets("Summary").Range("D6:D" & lastrow1)

    i = 10
    
              
        For Each cel In SrchRng2
        
            If InStr(1, cel.Value, "Total") > 0 Then
                
                For i = 10 To 395
                
                    Cells(cel.Row, i).Value = (Cells(cel.Row, i).Offset(, -4).Value + Cells(cel.Row, i).Offset(, -3).Value) / _
                    Cells(cel.Row, i).Offset(, -2).Value
                    
                    Cells(cel.Row, i).Offset(, 1).Value = (Cells(cel.Row, i + 1).Offset(, -5).Value + Cells(cel.Row, i + 1).Offset(, -4).Value) / _
                    Cells(cel.Row, i + 1).Offset(, -2).Value
                
                    i = i + 6
                    
                Next i
                                                    
            End If
            
        Next cel

 

[Marc L]

As we don't have any crystal ball you must check variables where the error occurs in order to catch why your logic fails …​

​

 

[NARAYANK991]

Hi ,

Which line of code is highlighted when this error is displayed ?

Can you upload your workbook with the data and code in it ?

Narayan

 

[Niraj Baraili]

Basically this file is generated after applying SubTotal function. All i need to do is to update the formula in Eff Growth and Full Growth column as (value1+value2)/value3 and (value1+value2)/value4 where there are 'Total' keywords in the column Team (A) and Level (Status).

 

[NARAYANK991]

Hi ,

Basically , when you divide by 0 , you generate this error.

Just include a statement such as :

If denominator <> 0 then calculate the expression.

See the attached file for the revised code.

Please note that any changes made by this macro cannot be undone ; backup your file under another name before you run the macro , so that you have a before and an after , which you can compare ; if there is anything wrong because of the macro , you can always go back to your previous version.

Narayan

 

[p45cal]

NARAYANK991 beat me to it. Just 1 thing to add; instead of this:

    For i = 8 To 86
      'do something
      i = i + 5
    Next i

you could have:

    For i = 8 To 86 Step 6
      'do something
    Next i

 

[Niraj Baraili]

Hi ,

Basically , when you divide by 0 , you generate this error.

Just include a statement such as :

If denominator <> 0 then calculate the expression.

See the attached file for the revised code.

Please note that any changes made by this macro cannot be undone ; backup your file under another name before you run the macro , so that you have a before and an after , which you can compare ; if there is anything wrong because of the macro , you can always go back to your previous version.

Narayan

Thank you Narayan. Code is running fine now.

 

[Niraj Baraili]

NARAYANK991 beat me to it. Just 1 thing to add; instead of this:

    For i = 8 To 86
      'do something
      i = i + 5
    Next i

you could have:

    For i = 8 To 86 Step 6
      'do something
    Next i

Thanks p45cal for pointing this out.