Loop till last row

Question

I have two columns A and B where, A= Days B= Bracket which depends on A

I have made code if else

        Sub AA()
    If Range("A2").Value <= 0 Then
            Range("B2").Value = "Not Due"
    
    ElseIf Range("A2").Value >= 1 And Range("A2").Value <= 30 Then
        Range("B2").Value = "1-30 Days"
    
    ElseIf Range("A2").Value >= 31 And Range("A2").Value <= 90 Then
        Range("B2").Value = "31-90 Days"
    
    ElseIf Range("A2").Value >= 91 And Range("A2").Value <= 180 Then
        Range("B2").Value = "91-180 Days"
    
    ElseIf Range("A2").Value >= 181 And Range("A2").Value <= 365 Then
        Range("B2").Value = "181-365 Days"
    
    ElseIf Range("A2").Value >= 366 And Range("A2").Value <= 730 Then
        Range("B2").Value = "1-2 Years"
    
    ElseIf Range("A2").Value >= 731 And Range("A2").Value <= 1095 Then
        Range("B2").Value = "2-3 Years"
    
    ElseIf Range("A2").Value >= 1096 Then
        Range("B2").Value = "Over 3 Years"
    End If 
End sub

Above code works on only one cell I need to run this code till the last row

Thanks

Start [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. — BigBen, Aug 11 '20 at 17:25
There are myriad examples of how to do this, on SE and the [internet more broadly](https://www.google.com/search?client=firefox-b-1-d&q=excel+loop+till+last+row), using your exact question. What have you tried? — BruceWayne, Aug 11 '20 at 17:30
Why use VBA? This is something easily solved with a lookup table. — Ron Rosenfeld, Aug 11 '20 at 17:51

score 0 · Answer 1 · answered Aug 11 '20 at 17:42

Excel does not understand "the last row": it's a spreadsheet that you can fill in as you wish.
You do have the possibility to go until the last cell in your column which is filled in (in VBA this is called .End(xlDown) for a Range object), but you really need to be careful: imagine you have an Excel sheet like this one:

        Col1   Col2
Row1  Val1.1 Val1.2
Row2  Val2.1 Val2.2

Rowa  Vala.1 Vala.2
Rowb  Valb.1 Valb.2

Imagine the focus is on Val1.2. When you do .End(xlDown) in VBA, or you press Ctrl+Down, you will go to Val2.2, not to Valb.2. So I advise you to be very cautious with that.

score 0 · Answer 2 · answered Aug 11 '20 at 18:15

    Sub Bracket()

Dim Cell As Range

  For Each Cell In Range("A2:A1048576")
    If Cell.Value <= 0 Then
      Cell.Offset(0, 1).Value = "Not Due"
    ElseIf Cell.Value >= 1 And Cell.Value <= 30 Then
      Cell.Offset(0, 1).Value = "1-30 Days"
    ElseIf Cell.Value >= 31 And Cell.Value <= 90 Then
      Cell.Offset(0, 1).Value = "31-90 Days"
    ElseIf Cell.Value >= 91 And Cell.Value <= 180 Then
      Cell.Offset(0, 1).Value = "91-180 Days"
    ElseIf Cell.Value >= 181 And Cell.Value <= 365 Then
      Cell.Offset(0, 1).Value = "181-365 Days"
    ElseIf Cell.Value >= 366 And Cell.Value <= 730 Then
      Cell.Offset(0, 1).Value = "1-2 Years"
    ElseIf Cell.Value >= 731 And Cell.Value <= 1095 Then
      Cell.Offset(0, 1).Value = "2-3 Years"
    ElseIf Cell.Value >= 1096 Then
      Cell.Offset(0, 1).Value = "2-3 Years"
         End If
  Next Cell
 
End Sub

score 0 · Accepted Answer · answered Aug 11 '20 at 19:21

Try this:

Sub subLoopRows()

    Dim lngRow As Long
    Dim lngLastUsedRow As Long

    'Finding out the last used row
    lngLastUsedRow = ActiveSheet.UsedRange.Rows.Count
    
    'Loop from the first row till the last used row. Set the start row as 2 if you have a header
    For lngRow = 1 To lngLastUsedRow
        
        With ActiveSheet
            
            If .Cells(lngRow, 1).Value <= 0 Then
            
                .Cells(lngRow, 2).Value = "Not Due"
                
            ElseIf .Cells(lngRow, 1).Value >= 1 _
                And .Cells(lngRow, 1).Value <= 30 Then
                
                .Cells(lngRow, 2).Value = "1-30 Days"
                
            ElseIf .Cells(lngRow, 1).Value >= 91 _
                And .Cells(lngRow, 1).Value <= 180 Then
                
                .Cells(lngRow, 2).Value = "91-180 Days"
                
            ElseIf .Cells(lngRow, 1).Value >= 181 _
                And .Cells(lngRow, 1).Value <= 365 Then
                
                .Cells(lngRow, 2).Value = "181-365 Days"
                
            ElseIf .Cells(lngRow, 1).Value >= 366 _
                And .Cells(lngRow, 1).Value <= 730 Then
                
                .Cells(lngRow, 2).Value = "1-2 Years"
        
            ElseIf .Cells(lngRow, 1).Value >= 731 _
                And .Cells(lngRow, 1).Value <= 1095 Then
                
                .Cells(lngRow, 2).Value = "2-3 Years"
                
            ElseIf .Cells(lngRow, 1).Value >= 1096 Then
                
                .Cells(lngRow, 2).Value = "Over 3 Years"
                
            End If
        
        End With
    
    Next lngRow

End Sub

Hope this was helpful!

Loop till last row

3 Answers3