Help adjusting a macro to split into groups of 1000

[mnty89]

Hi All, I posted something quite similar a while back.. but since then the requirements have changed.


The macro below takes one long list of values and converts them into 10 digits, adding preceding zeros if needed, then combines them into a comma separated string to use in a query.

I need help adjusting this to split the list inputted in the first column to groups of 1000. My knowledge of Vba isn't good enough, but I think this is quite an easy update to someone who is..

Basically I am hoping it can take the values in column A after the header row, so A2-A1000, execute the macro paste that into B2, then A1001-2001, and execute then paste into B3, etc until the list is finished..








Sub Stringv2()




Dim i, j As Integer
Dim s As String
Dim sPad As String
Const ForceText As String = "'"


tLength = 0
sPad = "0"
i = 1

Do Until Cells(i, 1).Value = ""
s = CStr(Cells(i, 1).Value)

j = 10 - Len(s)
Do Until j <= 0
s = "0" & s
j = j - 1
Loop

Cells(i, 1).Value = ForceText & s
i = i + 1
Loop
i = 1
s = ""
Do Until Cells(i, 1).Value = ""
s = s & Cells(i, 1).Value & ","
i = i + 1

Loop
i = 1
Cells(i, 2).Value = ForceText & s


End Sub

 

[Trevor_S]

I haven't been able to test this (or your existing macro), but the logic of my amendments is to create an outer loop looking for the end of the data, and change the existing loops to also stop if the row number is a multiple of 1000. I've added two new variables: k to store the value of i at the start of the first existing loop, so that i can be reset to that rather than 1 when the next loop is run; and l to act as a counter for the rows to write results to.

[COLOR=#333333]Sub Stringv2()[/COLOR]

[COLOR=#333333]Dim i, j, k, l As Integer[/COLOR]
[COLOR=#333333]Dim s As String[/COLOR]
[COLOR=#333333]Dim sPad As String[/COLOR]
[COLOR=#333333]Const ForceText As String = "'"[/COLOR]

[COLOR=#333333]tLength = 0[/COLOR]
[COLOR=#333333]sPad = "0"[/COLOR]
[COLOR=#333333]i = 1
[/COLOR]l = 1

[COLOR=#333333]Do Until Cells(i, 1).Value = ""
[/COLOR]k = i
[COLOR=#333333]Do Until Cells(i, 1).Value = "" Or i / 1000 = Int(i / 1000)[/COLOR]
[COLOR=#333333]s = CStr(Cells(i, 1).Value)[/COLOR]

[COLOR=#333333]j = 10 - Len(s)[/COLOR]
[COLOR=#333333]Do Until j <= 0[/COLOR]
[COLOR=#333333]s = "0" & s[/COLOR]
[COLOR=#333333]j = j - 1[/COLOR]
[COLOR=#333333]Loop[/COLOR]

[COLOR=#333333]Cells(i, 1).Value = ForceText & s[/COLOR]
[COLOR=#333333]i = i + 1[/COLOR]
[COLOR=#333333]Loop[/COLOR]
[COLOR=#333333]i = k[/COLOR]
[COLOR=#333333]s = ""[/COLOR]
[COLOR=#333333]Do Until Cells(i, 1).Value = "" Or i /1000 = Int(i / 1000)[/COLOR]
[COLOR=#333333]s = s & Cells(i, 1).Value & ","[/COLOR]
[COLOR=#333333]i = i + 1[/COLOR]

[COLOR=#333333]Loop[/COLOR]

[COLOR=#333333]Cells(l, 2).Value = ForceText & s[/COLOR]
l = l + 1
Loop

[COLOR=#333333]End Sub
[/COLOR]

 

[mnty89]

Thanks! This appears to start to work then gives me the overflow error on this line-

l = l + 1

 

[Joe4]

Note that this:

[COLOR=#333333]Dim i, j, k, l As Integer[/COLOR]

does not do what you think it does.
This will declare l to be an Integer, but i, j, and k will be declared to be Variant (which means they would accept string entries).

Each variable needs to be explicitly declared like this:

[COLOR=#333333]Dim i [/COLOR][COLOR=#333333]As Integer[/COLOR][COLOR=#333333], j[/COLOR][COLOR=#333333] As Integer[/COLOR][COLOR=#333333], k[/COLOR][COLOR=#333333] As Integer[/COLOR][COLOR=#333333], l As Integer[/COLOR]

or

[COLOR=#333333]Dim i [/COLOR][COLOR=#333333]As Integer
[/COLOR][COLOR=#333333]Dim j[/COLOR][COLOR=#333333] As Integer
[/COLOR][COLOR=#333333]Dim k[/COLOR][COLOR=#333333] As Integer
[/COLOR][COLOR=#333333]Dim l As Integer[/COLOR]

As for your error, note that l is declared to be an Integer. The upper limit on integer is 32,767. If your value may exceed that, you will need to use a different type, like Long.

 

[Trevor_S]

Thanks Joe4, hadn't spotted that, I'd been looking more at what needed to be added, rather than check the original code.

Technically, for this code, the variables don't need to be dimensioned at all (i.e. all Dim... lines could be removed), although its probably best practice to include them as Joe4 has shown, but with l changed from Integer to Long.

 

[SpillerBD]

Technically, for this code, the variables don't need to be dimensioned at all (i.e. all Dim... lines could be removed), although its probably best practice to include them as Joe4 has shown, but with l changed from Integer to Long.

I moved away from the "don't need to be dimensioned" camp to Explicit (always DIM the variables) camp. Part of that is the size of data sets I work with. Variant (or non-dimensioned variables) items cause Excel to set aside much larger memory spaces than any of the numeric dimensions, LONG or DOUBLE.
Integer I avoid unless I can know for certain the variable will never push its limitations as its not in the general math sense.
Oracle has provided my employer a macro for data import that uses Integer which caused a surprising failure.

 

[Joe4]

Yes, it is always a good idea to declare all your variables. If you put "Option Explicit" at the top of your module, it will force you to declare them all.
In addition to the the memory benefits that Spiller BD mentions, it also helps with error debugging as well as preventing typos, errors, and unexpected results.