Creating Duplicate Info

[Hinojosa via OfficeKB.com]

I have five cells of info that need to be added to over 2000 other sku's.

Example I have A, B, C, D, E and 1 - 2000 I need it to be 1A, 1B, 1C, 1D, 1E,
2A, 2B etc. I was wondering is there a faster way instead of insterting 5
lines 2000 times?

 

[Chip Pearson]

You can do it easily with code.

Sub AAA()
Dim Letters(1 To 5) As String
Dim LNdx As Long
Dim N As Long
Dim RowNum As Long
Letters(1) = "A"
Letters(2) = "B"
Letters(3) = "C"
Letters(4) = "D"
Letters(5) = "E"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For N = 1 To 2000
For LNdx = LBound(Letters) To UBound(Letters)
RowNum = RowNum + 1
Cells(RowNum, "A").Value = CStr(N) & Letters(LNdx)
Next LNdx
Next N
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

It runs in less than one second to create 10000 entries.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Hinojosa via OfficeKB.com]

My apologizes for being specfic, Column has A random set of numbers in which
I need to duplicate that number five times and add a statement onto each one.
I can use the Concatenate formula to add my statement (because they are five
different statements) I just don't know how to duplicate the numbers.

 

[Chip Pearson]

Try code like the following. Change the lines marked with <<< to suit
your needs.


Sub AAA()
Dim Statements(1 To 5) As String
Dim R As Range
Dim Dest As Range
Dim LastRow As Long
Dim N As Long

'<<<< CHANGE THESE VALUES
Statements(1) = "one"
Statements(2) = "two"
Statements(3) = "three"
Statements(4) = "four"
Statements(5) = "five"

' set to first cell of existing data
Set R = Worksheets("Sheet1").Range("A1") '<<<

' set to destination for new values
Set Dest = Worksheets("Sheet2").Range("B1") '<<<

With R.Worksheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Do Until R.Row > LastRow
For N = LBound(Statements) To UBound(Statements)
Dest.Value = R.Value & Statements(N)
Set Dest = Dest(2, 1)
Next N
Set R = R(2, 1)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com