Help adjusting existing macro that creates a comma separated string

[mnty89]

I was passed down this macro that joins a list of entries in column A, into one continuous string separated by a comma and makes it 10 digits longs. If it is shorter than 10 it adds zeroes to the front.

I don't really see what this macro is doing enough to tweak it, but essentially what I want is for it to perform this but make a new one every 1000 entries.

So basically it would take the data in column A, and make the entries in rows 1-1000, a joined string of 10 digit long values separated only by 1 comma. Then 1001-2000 the same etc..

Can anyone provide a tweak or new macro that could do this? It could place the results in B1, B2, B3 etc as each grouping of 1000 is created.





Sub generatecsv()


Dim i As Integer
Dim s As String


i = 1


Do Until Cells(i, 1).Value = ""
If (s = "") Then
s = Cells(i, 1).Value
Else
s = s & "," & Cells(i, 1).Value
End If
i = i + 1
Loop


Cells(1, 2).Value = s


End Sub

 

[tonyyy]

Sub generatecsv()
Dim i As Long, j As Long, s As String
i = 1
j = 1
Do Until Cells(i, 1).Value = ""
    If s = "" Then
        s = Cells(i, 1).Value
        Cells(j, 2).Value = s
    Else
        s = s & "," & Cells(i, 1).Value
        If i Mod 1000 = 0 Then
            Cells(j, 2).Value = s
            j = j + 1
            s = ""
        Else
            Cells(j, 2).Value = s
        End If
    End If
    i = i + 1
Loop
End Sub

Cheers,

tonyyy