CODEWITHSUNDEEP
excelvba
Stephanie1010
Stephanie1010

Reputation: 31

Excel VBA Loop - Copying cells from one column into another multiple times

I'm trying to create a loop that pulls in the value on the second image.

Image 1 - Document1 Image 2 - End Result of Loop

Upvotes: 1

Views: 176

Answers (1)

dekingsey
dekingsey

Reputation: 66

Here is how you could do it, with two embedded loops.

Sub CopyStates()
Dim StateRow%
Dim CopyRow%
Dim FruitRow%
Dim FruitMaxRow%

'these three constants define in which columns your data can be found
Const STATE_COL = 1
Const COPY_COL = 6
Const FRUIT_COL = 7

'these constant define the start row of your data
Const DATA_START_ROW_S = 3 'start row for states
Const DATA_START_ROW_P = 2 'start row for fruits and copy

FruitMaxRow = DATA_START_ROW_P
StateRow = DATA_START_ROW_S

'first, check how many fruits you have
While Cells(FruitMaxRow, FRUIT_COL) <> ""
    FruitMaxRow = FruitMaxRow + 1
Wend

'initialize: begin copying at the first empty row where you had fruits
CopyRow = FruitMaxRow

'loop through all the states
While Cells(StateRow, STATE_COL) <> ""
    'loop through all the fruits
    For FruitRow = DATA_START_ROW_P To FruitMaxRow - 1
        'copy the state and fruit
        Cells(CopyRow, COPY_COL) = Cells(StateRow, STATE_COL)
        Cells(CopyRow, FRUIT_COL) = Cells(FruitRow, FRUIT_COL)
        CopyRow = CopyRow + 1
    Next FruitRow
    StateRow = StateRow + 1
Wend


End Sub

Upvotes: 2

Related Questions