Error in my code Please help

[JawaharPrem]

Hi All,

i need to updated mentioned slab for avg amt till the last coloum of data iam getting error in "loop" Please help on same.

Sub slab_1()
Dim i As Integer
i = i + 1
Loop
For i = 1 To 10000
Exit For
Else
If (Cells(i, 40) <= 500) Then
Cells(i, 41) = "0-500"
Else
If (Cells(i, 40) <= 750) Then
Cells(i, 41) = "501-750"
Else
If (Cells(i, 40) <= 1000) Then
Cells(i, 41) = "751-1000"
Else
If (Cells(i, 40) > 1000) Then
Cells(i, 41) = "Grt 1K"
End If
End If
End If
End If
Next i
End Sub

 

[NARAYANK991]

Hi ,

The following two statements should not exist in the code you have posted ; it would help if you could upload your workbook with the data and code in it.

Loop
Exit For

Even the statement :

i = i + 1

is not relevant , and can be done away with.

Narayan

 

[JawaharPrem]

Hi Narayanan,

enclosed my file, avg billing in 40 coloum mentioend code, Please paste the code.

Sub slab_1()
Dim i As Integer
i = i + 1
Loop
For i = 1 To 10000
Exit For
Else
If (Cells(i, 40) <= 500) Then
Cells(i, 41) = "0-500"
Else
If (Cells(i, 40) <= 750) Then
Cells(i, 41) = "501-750"
Else
If (Cells(i, 40) <= 1000) Then
Cells(i, 41) = "751-1000"
Else
If (Cells(i, 40) > 1000) Then
Cells(i, 41) = "Grt 1K"
End If
End If
End If
End If
Next i
End Sub

 

[vletm]

JawaharPrem
Test this version ...

Sub ANtoAO()
    Application.ScreenUpdating = False
    With ActiveSheet
        an_max = .UsedRange.Rows.Count
        For an = 2 To an_max
            chk_ac = .Cells(an, 40)
            msg = Empty
            If Len(chk_ac) > 0 Then msg = "0-500"
            If chk_ac > 500 Then msg = ">500-750"
            If chk_ac > 750 Then msg = ">750-1000"
            If .Cells(an, 40) > 1000 Then msg = "Grt >1K"
            .Cells(an, 41) = msg
        Next an
    End With
    Application.ScreenUpdating = True
End Sub

... then need to write only once the result.
ps how ex do msg '0-500' and '500-750' can be possible?
if 500 gives '0-500' and 501 gives '500-750', really?

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[JawaharPrem]

Hi Thanks for Narayanan and Vletm both coding is working.


Hi Naranayan. one question in your coding, why coding in not in proper line is there is any reason. and if value is 0 in the slab "0-500" is not updating.

Thanks
Jawahar

 

[JawaharPrem]

JawaharPrem
Test this version ...

Sub ANtoAO()
    Application.ScreenUpdating = False
    With ActiveSheet
        an_max = .UsedRange.Rows.Count
        For an = 2 To an_max
            chk_ac = .Cells(an, 40)
            msg = Empty
            If Len(chk_ac) > 0 Then msg = "0-500"
            If chk_ac > 500 Then msg = ">500-750"
            If chk_ac > 750 Then msg = ">750-1000"
            If .Cells(an, 40) > 1000 Then msg = "Grt >1K"
            .Cells(an, 41) = msg
        Next an
    End With
    Application.ScreenUpdating = True
End Sub

... then need to write only once the result.
ps how ex do msg '0-500' and '500-750' can be possible?
if 500 gives '0-500' and 501 gives '500-750', really?

this is new to me i will try this logic in some other reports also. thanks for new one.

 

[NARAYANK991]

Hi Thanks for Narayanan and Vletm both coding is working.


Hi Naranayan. one question in your coding, why coding in not in proper line is there is any reason. and if value is 0 in the slab "0-500" is not updating.

Thanks
Jawahar

Hi ,

Go through the code , and see the first IF statement within the For ... Next loop ; if you remove this IF and its associated END IF statements , then every cell will have its corresponding entry.

Narayan

 

[JawaharPrem]

OK Thanks,

i will check and confirm.