Excel VBA Nested Loops to start count from 0 again

Question

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!

I reread and reread your sentence but can t be sure what you want. What does work and what do you want? — Pierre44, Aug 28 '18 at 08:41
I want to concatenate numbers to the value in each cell. For example, cells A1, A2, A3, A4, A5 have values "apple". I want to add numbers that counts from 1 to 5 in each cell, and repeats from 1 when it ended at 5 earlier. So i wll get A1 = "apple1", A2 = "apple2", A3 = "Apple3", A4 = "Apple4", A5 = "Apple5", A6 = "Apple1", A7 = "Apple2".... — Jing Yi, Aug 28 '18 at 08:42
rowNum seems to be unnecessary. You are already calculating the last row with your LR variable. — Shazu, Aug 28 '18 at 08:44
Please note that `Dim q, i, rowStart, rowEnd , rowNum, LR as Long` only declares `LR as Long` but all the others as `Variant`. You must specify a type for **every** variable in VBA: `Dim q as Long, i as Long, rowStart as Long, rowEnd as Long, rowNum as Long, LR as Long` otherwise VBA assumes automatically `Variant`. — Pᴇʜ, Aug 28 '18 at 08:44

iDevlop · Answer 1 · 2018-08-28T08:54:37.777

2

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

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

edited Aug 28 '18 at 08:54

answered Aug 28 '18 at 08:48

iDevlop

24,841
11
90
149

score 2 · Accepted Answer · answered Aug 28 '18 at 08:54

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

Lina · Answer 3 · 2018-09-26T03:28:43.500

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

QHarr · Answer 4 · 2018-08-28T10:24:29.533

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

score 0 · Answer 5 · answered Aug 28 '18 at 08:50

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

score 0 · Answer 6 · answered Aug 28 '18 at 09:35

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

score 0 · Answer 7 · answered Aug 28 '18 at 10:02

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

Excel VBA Nested Loops to start count from 0 again

7 Answers7