How to define age bracket using Loop

[ThrottleWorks]

Hi,

I am trying to populate Age Bracket.

I am trying to populate these by using Loop.

I do not know how to define "from" and "to" in a loop.

For example how do I define greater than 7 and lesser than 16 in a Loop.
I tried doing it by "If Rng.Value > 7 And Rng.Value < 16 Then" but it's not working.

I am aware that I can populate it by using formula but is there a way where I can define values mentioned below.

Can anyone please help me in this.

For Each Rng In Rng6
        If Rng.Value <= 7 Then
            Cells(Rng.Row, LastCol3).Value = "0-7"
        End If
        If Rng.Value > 7 And Rng.Value < 16 Then
            Cells(Rng.Row, LastCol3).Value = "8-15"
        End If
    Next

 

[ThrottleWorks]

Hi,

I used below mentioned code. I am aware it is not the right way to do but as of now it is working.

Can anyone please suggest a better way.

For Each Rng In Rng6
        If Rng.Value <> "" Then
            If Rng.Value <= 7 Then
                Cells(Rng.Row, LastCol3).Value = "0-7"
            End If
        End If
        If Rng.Value > 7 Then
            If Rng.Value <= 15 Then
                Cells(Rng.Row, LastCol3).Value = "'8-15"
            End If
        End If
        If Rng.Value > 15 Then
            If Rng.Value <= 30 Then
                Cells(Rng.Row, LastCol3).Value = "16-30"
            End If
        End If
        If Rng.Value > 30 Then
            If Rng.Value <= 60 Then
                Cells(Rng.Row, LastCol3).Value = "31-60"
            End If
        End If
        If Rng.Value > 60 Then
            If Rng.Value <= 100 Then
                Cells(Rng.Row, LastCol3).Value = "61-100"
            End If
        End If
        If Rng.Value > 100 Then
            Cells(Rng.Row, LastCol3).Value = "100+"
        End If
    Next

 

[Chihiro]

I think nested loops would work.

See here for reference.
http://asaanfunda.com/tutorials/learnexcelmacros-c-18

 

[NARAYANK991]

Hi Sachin ,

See if this works.

    For Each Rng In Rng6
        If Rng.Value <> vbNullString Then
          Select Case Rng.Value
                  Case Is > 100
                      Cells(Rng.Row, LastCol3).Value = "100+"
                  Case Is > 60
                      Cells(Rng.Row, LastCol3).Value = "61-100"
                  Case Is > 30
                      Cells(Rng.Row, LastCol3).Value = "31-60"
                  Case Is > 15
                      Cells(Rng.Row, LastCol3).Value = "16-30"
                  Case Is > 7
                      Cells(Rng.Row, LastCol3).Value = "8-15"
                  Case Else
                      Cells(Rng.Row, LastCol3).Value = "0-7"
          End Select
        End If
    Next

Narayan

 

[chirayu]

For Each Rng In Rng6

    '0-7
    If Not IsEmpty(Rng) _
    And Rng.Value <= 7 Then
        Cells(Rng.Row, LastCol3).Value = "0-7"
   
    '8-15
    ElseIf Rng.Value > 7 _
    And Rng.Value <= 15 Then
        Cells(Rng.Row, LastCol3).Value = "'8-15"
   
    '16-30
    ElseIf Rng.Value > 15 _
    And Rng.Value <= 30 Then
        Cells(Rng.Row, LastCol3).Value = "16-30"
   
    '31-60
    ElseIf Rng.Value > 30 _
    And Rng.Value <= 60 Then
        Cells(Rng.Row, LastCol3).Value = "31-60"
   
    '61-100
    ElseIf Rng.Value > 60 _
    And Rng.Value <= 100 Then
        Cells(Rng.Row, LastCol3).Value = "61-100"
       
    '100+
    ElseIf Rng.Value > 100 Then
        Cells(Rng.Row, LastCol3).Value = "100+"
    End If
   
Next

 

[ThrottleWorks]

Hi @NARAYANK991 Sir, thanks a lot for the help. It's working perfectly.

@chirayu , thanks a lot for the help.

 

[Deepak]

No need of loop..

Try something like as ..

Range("B1:B10") = _
    "=LOOKUP(A1,{0,8,16,31,61,101},{""0-7"",""8-15"",""16-30"",""31-60"",""61-100"",""100+""})"
Range("B1:B10").Value = Range("B1:B10").Value

 

[ThrottleWorks]

Hi @Deepak Sir, thanks a lot for the help. I am checking this and will revert shortly.