CODEWITHSUNDEEP
vbaexcel
Jing Yi
Jing Yi

Reputation: 203

Excel VBA Nested Loops to start count from 0 again

I am writing a script to print in a message box, the cell value and repetitive number counts from 1-5.

Currently, I have a for loop that counts the total number of rows I have in my spreadsheet. I am unsure of how to add another for loop (nested for loop) to call the program to add 1 to 5 to the first 5 rows, and restart at 1 to 5 again at the 6th row, and so on.

For example,

If values in cells A1 to A10 are "Apple" respectively, I want to concetenate numbers from 1 to 5 such that I get the results below:

A1 = "Apple1"
A2 = "Apple2"
A3 = "Apple3"
A4 = "Apple4"
A5 = "Apple5"
A6 = "Apple1" 'it starts from 1 again
A7 = "Apple2"

and so on

Below is my sample code:

Option Explicit
Sub appendCount()
    Dim q, i, rowStart, rowEnd , rowNum, LR as Long

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    rowNum = Range("A1:A" & LR).Count

    For q = 1 To rowNum Step 1
        If Not IsNull(Range("A" & q)) Then
        For i = 1 to 5        
            MsgBox Range("A" & q).Value & i
        Next i
        End If
    Next q
End Sub

Any help would be greatly appreciated!

Upvotes: 0

Views: 1217

Answers (7)

Lina
Lina

Reputation: 291

I understand your question is values in cells A1 to A10 are "Apple" respectively, you want to content Numbers from 1 to 5, then A6 to A10 content Numbers are also from 1 to 5.

This my test code, you can try it:

Option Explicit

Sub appendCount()
    Dim q, i, cou, rowStart, rowEnd, rowNum, LR As Long

LR = Cells(Rows.count, 1).End(xlUp).Row
rowNum = Range("A1:A" & LR).count
cou = 1

For q = 1 To rowNum Step 1
    If Not IsNull(Range("A" & q)) Then

      For i = 1 To 5
        MsgBox Range("A" & q).Value & cou
        cou = cou + 1
        If cou = 6 Then
            cou = 1
        End If
      Next i
    End If
Next q
End Sub

Upvotes: 1

QHarr
QHarr

Reputation: 84465

Working with arrays will be faster. Also, mod will fail with large numbers so the below is written to handle large numbers. The point to start renumbering is also put into a constant to allow easy access for changing. Code overall is thus more flexible and resilient.

Option Explicit
Public Sub AddNumbering()
    Dim arr(), i As Long, lastRow As Long, index As Long
    Const RENUMBER_AT = 6

    With ThisWorkbook.Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Select Case lastRow
        Case 1
            ReDim arr(1, 1): arr(1, 1) = .Range("A1").Value
        Case Else
            arr = .Range("A1:A" & lastRow).Value
        End Select
        index = 1
        For i = LBound(arr, 1) To UBound(arr, 1)
            If arr(i, 1) <> vbNullString Then
                If i - (CLng(i / RENUMBER_AT) * RENUMBER_AT) <> 0 And i <> 1 Then
                    index = index + 1
                Else
                    index = 1
                End If
                arr(i, 1) = arr(i, 1) & CStr(index)
            End If
        Next
        .Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    End With
End Sub

Upvotes: 1

Jeremy
Jeremy

Reputation: 1337

This would do it:

Sub Loops()
Dim i As Long, iMultiples As Long, iMultiple As Long

iMultiples = WorksheetFunction.Ceiling_Math(Cells(Rows.Count, 1).End(xlUp).Row, 5, 0) ' this rounds up to the nearest 5 (giving the number of multiples

For iMultiple = 1 To iMultiples
    For i = 1 To 5
        If Not IsNull(Range("A" & i).Value) Then Range("A" & i).Value = "Apple" & i 'This can be tweaked as needed
    Next
Next


End Sub

Upvotes: 0

nick
nick

Reputation: 630

Sub appendCount()
Dim q, c, i, rowStart, rowEnd, rowNum, LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row
rowNum = Range("A1:A" & LR).Count

c = 1
For q = 1 To rowNum Step 1
    If Not IsEmpty(Range("A" & q)) Then
    If (c Mod 6) <> 0 Then
    Range("B" & q).Value = Range("A" & q).Value & (c Mod 6)
    Else
    c = c + 1
    Range("B" & q).Value = Range("A" & q).Value & (c Mod 6)
    End If
    End If
c = c + 1
Next q

End Sub

Upvotes: 0

Xabier
Xabier

Reputation: 7735

I believe the following will do what you expect, it will look at the values on Column A and add the count to them on Column B:

Option Explicit

Sub appendCount()
    Dim LR As Long, rownumber As Long, counter As Long
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    'declare and set the worksheet you are working with, amend as required
    counter = 0
    LR = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For rownumber = 1 To LR Step 1
        If Not IsEmpty(ws.Range("A" & rownumber)) Then
            counter = counter + 1
            If counter = 6 Then counter = 1
            ws.Range("B" & rownumber).Value =ws.Range("A" & rownumber).value & counter
        End If
    Next rownumber
End Sub

Upvotes: 2

iDevlop
iDevlop

Reputation: 25262

IsNull() on a cell will always return False. Replace IsNull by IsEmpty,
or use someCell <> "".

See https://stackoverflow.com/a/2009754/78522

Upvotes: 2

jkpieterse
jkpieterse

Reputation: 2986

Your declaration is wrong, despite what you might expect these variables are NOT declared as Long but as Variant: q, i, rowStart, rowEnd , rowNum you must include the type for each variable separately.

This code should do the trick for you:

Sub appendCount()
    Dim q As Long, LR As Long, rowNum As Long

    LR = Cells(Rows.Count, 1).End(xlUp).Row
    rowNum = Range("A1:A" & LR).Count

    For q = 1 To rowNum Step 1
        If Not Len(Range("A" & q).Value) = 0 Then
            If q Mod 5 = 0 Then
                MsgBox Range("A" & q).Value & 5
            Else
                MsgBox Range("A" & q).Value & (q Mod 5)
            End If
        End If
    Next q
End Sub

Upvotes: 0

Related Questions