CODEWITHSUNDEEP
vbaexcel
user3846393
user3846393

Reputation: 15

VBA: fill a column with a repeating list of values from another column

In column B of Sheet1 I have a list of divisions that coincide with ship dates. I need the divisions to be copied from Sheet1(columnB) to Sheet2(columnC). The divisions go from row 3 to 17 and once the macro gets to the division in row 17 I need it to start over with the division in row 3 and add to the bottom of the column. Here is what I have but it doesn't give me any output.

For i = 2 To 2
    For y = 3 To 17
        x = x + 1
        Sheets("Sheet2").Cells(x, 3).Value = Sheets("Sheet1").Cells(y, i).Value
    Next y
Next i

Input:

Atlanta  
Cincinnati  
Columbus  
Michigan  
Central  
Louisville  
Delta  
Nashville  
Mid-Atlantic  
Southwest  
Charleston  
Indiana  
Southwest  
Dillon  
California

Output:

Atlanta  
Cincinnati  
Columbus  
Michigan  
Central  
Louisville  
Delta  
Nashville  
Mid-Atlantic  
Southwest  
Charleston  
Indiana  
Southwest  
Dillon  
California  
Atlanta  
Cincinnati  
Columbus  
Michigan  
Central  
Louisville  
Delta  
Nashville  
Mid-Atlantic  
Southwest  
Charleston  
Indiana  
Southwest  
Dillon  
California

Upvotes: 1

Views: 2491

Answers (4)

Rick
Rick

Reputation: 45251

You can accomplish this pretty easily without VBA. In column C of Sheet2, enter:

=INDEX($B$3:$B$17,MOD(ROW(C1)-ROW($C$1),COUNTA($B$3:$B$17))+1)

And just copy down as far as you want to go. Change $C$1 to the first row of the Sheet2 column.

OPTIONAL:

I also suggest using Named Ranges to make life easier when you come back a year later and can't remember what you were doing. So, do:

Formulas->Define Name->DivisionsList in Name field, =Sheet1!$B$3:$B$17 in Refers to: field->OK

And:

Formulas->Define Name->FirstRow in Name field, =Sheet2!$C$1 in Refers to: field->OK

Then just enter this in Column C of Sheet2 and copy down:

=INDEX(DivisionsList,MOD(ROW(C1)-ROW(FirstRow),COUNTA(DivisionsList))+1)

Change the address of FirstRow as necessary.

Upvotes: 0

Tom
Tom

Reputation: 9878

Try:

set sht1 = ThisWorkbook.sheets("Sheet1")
set sht2 = ThisWorkbook.sheets("Sheet2")

set rng = sht1.Columns(2).UsedRange
j = 1 ' Change for where you want it to start

for each cell In rng.cells
    sht2.cells(1,j) = cell
    j = j + 1
next cell

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

You can do this with a single loop, might be easier if you instantiate a few range variables. As others have noted, your outer loop is beginning at "2" and ending at "2", so that is why it is not repeating as you intend.

Sub fillValues()

Dim i As Integer
Dim howManyTimes as Integer
Dim copyRange As Range
Dim pasteRange As Range
Dim rowCount as Long

howManyTimes = 2   'modify as needed; tells the procedure how many times to loop

'## Define the range to "copy"
Set copyRange = Sheets("Sheet1").Range("C3:C17")
'## Get the # of rows in this range
rowCount = copyRange.Rows.Count

'## Define the original destination to "paste":
Set pasteRange = Sheets("Sheet2").Range("A3") 'this will be modified later


'## Loop and input the values:
For i = 1 To howManyTimes
    pasteRange.Offset((i - 1) * rowCount).Resize(row.Count).Value = copyRange.Value
Next

End Sub

Upvotes: 1

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

Your Outer For Loop For i = 2 To 2 runs only once, change it to For i = 1 To 2 to run two times and n to run n times etc 

x = 3
For i = 1 To 2
    For y = 3 To 17            
        Sheets("Sheet2").Cells(x, 3).Value = Sheets("Sheet1").Cells(y, 2).Value
        x = x + 1
    Next y        
Next i

Upvotes: 1

Related Questions