Loop that replicate my formula until last cell of my row-CodePudding

My aim is to have a formula that fills the empty cells with the previous Q. question, until the last non empty cell (see picture)

The range is the last non empty cell of my row.

For now my code looks like this :

Sub Range_End_Exemple()
    
    Dim cell_target As Range
    ActiveCell.FormulaR1C1 = "= IF(ISBLANK(R[-2]C)=TRUE,RC[-1],R[-2]C)"
    
    Set cell_target = Worksheets("dataset Feedback forms").Range(Cells(1, Columns.Count).End(xlToLeft).Select Type:xlFillDefault
    
End Sub

Thanks for you help if you have any suggestion.

CodePudding user response：

so I'd imagine something like this:

lrow = Cells.Find("*", Cells(1, 1), xlValues, xlPart, xlByColumns, xlPrevious, False).Column ' <-- this gives you the last column blank

nextblank = Cells.Find("", Cells(1, 1), xlFormulas, xlByColumns, xlNext, False).Column ' <-- this gives you the FIRST blank column number

ltr = Split(Cells(1, nextblank - 1).Address, "$")(1) ' <-- this gives you the letter

aux = Range(ltr & "1") ' <-- this is the value you need to copy

After the first nextblank statement you need to use this to iterate

nextblank = Cells.Find("", Cells(1, nextblank), xlFormulas, xlByColumns, xlNext, False).Column

Using those values -> lrow doesn't change, it's your final destination

nextblank,ltr and aux values changes after you copy

Hope it helps!

CodePudding user response：

This will do what you appear to want from your image:

Sub Propagate()
    Dim lastCol As Integer
    With Worksheets("dataset Feedback forms")
        lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        With .Cells(1, 4).Resize(1, lastCol - (4 - 1)).SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = "=RC[-1]"
            .Value2 = .Value2
        End With
    End With
End Sub