Copying strings and using counters

[Gumby99]

Here is what I am trying to do.

I have a spreadsheet with 3 values; a text string ("ABC123"), the first
number in a counter ("100") and a number ("10"). The number is the
number of line-items I need, the text string need to be the same on
each line and the count needs to be incremented by one for each line.
So basically I am trying to come up with a macro that will have the
following output.

ABC123 100
ABC123 101
ABC123 102
ABC123 103
ABC123 104
ABC123 105
ABC123 106
ABC123 107
ABC123 108
ABC123 109

Any ideas on how this would be accomplished?

 

[ChadF]

Hello,

Several ways you could do that. Based on what you mention, first
thought that comes to mind:

Public Sub myBuild()
Dim StringReference as String
Dim StartNumber as Integer
Dim Count as Integer
Dim Iter as Integer
Dim myRange as Range



' Let A1 be the string ABC123
' Let A2 be the start number
' Let A3 be the count

Set myRange = Cells(Range("A4").row, Range("A4").Column)

StringReference = Range("A1").text
StartNumber = Range("A2").value
Count = Range("A3").Value

For Iter = StartNumber to StartNumber + Count
myRange.value = StringReference & " " & Iter
Set myRange = myRange.Offset(1, 0)
Next Iter

' Stuffs the output into Column A, starting at row 4.


End Sub

Is that what you have in mind ?
Chad

 

[paul.hobin]

This code assumes the values you mention are in cells A1, B1 and C1, and you
want to output your list starting in cell A2 and going down. You don't
actually have to assign the values in A1, B1 and C1 to variables; you can
simply reference the cells in your code wherever the values in those cells
are required, but the code is easier to understand if you use meaningful
variable names instead of cell references.

By the way, this is the first time I've responded to anything.......if you
find it useful, I would be thrilled!

Sub Test()
Dim i As Integer
Dim Txt As String
Dim StartValue As Integer
Dim Repetitions As Integer

Txt = Cells(1, 1)
StartValue = Cells(1, 2)
Repetitions = Cells(1, 3)

Cells(2, 1).Activate

For i = StartValue To StartValue + Repetitions - 1
ActiveCell = Txt & " " & i
ActiveCell.Offset(1, 0).Activate
Next i

End Sub

 

[Gary''s Student]

Sub tablemaker()
Dim s1 As Worksheet, s2 As Worksheet, r As Range
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

k = 1
For i = 1 To nLastRow
Label = s1.Cells(i, 1).Value
Start = s1.Cells(i, 2).Value
Count = s1.Cells(i, 3).Value
For j = 1 To Count
s2.Cells(k, 1).Value = Label
s2.Cells(k, 2).Value = Start + j - 1
k = k + 1
Next
Next
End Sub


So if Sheet1 started with:

alpha 12 4
beta 100 6
gamma 2 7

Then the macro would produce on Sheet2:

alpha 12
alpha 13
alpha 14
alpha 15
beta 100
beta 101
beta 102
beta 103
beta 104
beta 105
gamma 2
gamma 3
gamma 4
gamma 5
gamma 6
gamma 7
gamma 8