asked on

Excel VBA to cycle through record list and apply a vaule to a number of rows based on another column of data

Have a list of numbers (column A) and want to cycle through the list and add the number (from column A) against the number of rows down the worksheet that appear in column B using VBA... any ideas?

In the attached file i've described what the output should be for the macro in VBA.

Sheet1 is the setup of how many records should be listed against each
Sheet2 is the output with how it should look like at the end.

Sample.xlsm

Louis LIETAER

Hi, I don't understand where references are coming from ?

Ryan Chong

A quick test like this:

Sub test()
    Dim ws_src_name As String, ws_target_name As String
    Dim ws_src As Worksheet, ws_target As Worksheet
    Dim v As Variant, row As Integer, replicateCnt As Integer, running As Integer
    
    ws_src_name = "Sheet1"
    ws_target_name = "Sheet3"
    
    Set ws_src = Sheets(ws_src_name)
    
    Application.DisplayAlerts = False
    For Each ws_target In ThisWorkbook.Worksheets
        If ws_target.Name = ws_target_name Then
            ws_target.Delete
            Exit For
        End If
    Next
    
    Set ws_target = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws_target.Name = ws_target_name
    
    row = 2
    running = 1
    
    ws_target.Cells(1, 1) = "Numbers"
    v = ws_src.Cells(row, 1)
    replicateCnt = ws_src.Cells(row, 2)
    
    Do While IsEmpty(v) = False
        If IsNumeric(replicateCnt) Then
            For i = 1 To replicateCnt
                Debug.Print (running + i) & " = " & v
                ws_target.Cells(running + i, 1) = v
            Next
            running = running + replicateCnt
        Else
            Exit Do
        End If
        row = row + 1
        v = ws_src.Cells(row, 1)
        replicateCnt = ws_src.Cells(row, 2)
    Loop
    
    Application.DisplayAlerts = True
    
    MsgBox "Done!"
End Sub

Open in new window

Chris_Sizer

ASKER

Hi @Ryan Chong,

This looks like the solution I need but how do i make the code replace what is already in column A on Sheet2 without disrupting the data in Column B?

Thanks,

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Chris_Sizer

ASKER

Perfection @Ryan Chong - just what I needed :)