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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,784
Office Version
  1. 2010
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,030
Messages
5,835,038
Members
430,333
Latest member
jaypatel1298

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