Help adjusting existing macro that creates a comma separated string

mnty89

Board Regular
Joined
Apr 1, 2016
Messages
66
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top