I'm trying to write a VBA code that would apply the count column and paste the existing value in that row 8, or 4 times, etc. I tried using the Not IsEmpty function and loop to start at each row where the column is not empty, then paste a certain amount of times behind it. But it just won't work. Any ideas? Here's what I have and what I want it to look like. My code is probably really wrong.

• Let's say that n is a value in column A: counting from the last column towards the left, it takes the n-th value to fill the n-1 right-most columns with it.

Sub FillRight()

    ' Read.

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range, rCount As Long, cCount As Long

    With ws.UsedRange
        rCount = .Rows.Count - 1
        Set rg = .Resize(rCount).Offset(1) ' without headers
        cCount = .Columns.Count
    End With

    Dim Data() As Variant: Data = rg.Value
    
    ' Modify.
    
    Dim r As Long, c As Long, cNum As Long, cValue As Variant
    
    For r = 1 To rCount
        If Len(CStr(Data(r, 1))) > 0 Then
            cNum = cCount - Data(r, 1)   1
            cValue = Data(r, cNum)
            For c = cNum   1 To cCount
                Data(r, c) = cValue
            Next c
        End If
    Next r

    ' Write.
       
    rg.Value = Data
 
End Sub

CodePudding user response：

@VBasic2008. here's the outcome that the code is giving me, on row 5, 2000 should be pasted from 6 to 7(According to the header). But I think the code only works if the value on column A is 3.