Search code examples
excelvbadataset

Looping values from specific cell to a column in Excel

I would like to have a macro in which by providing a value in cell E3, the value of E3 is copied and pasted in cell K17. Then I will modify the value in E3 to populate cell K18, and so on and so forth.

This is the current code I have, but of course it's limiting me to add the desired value from cell E3 to be always pasted in cell K17:

Sub box_value()
    Range("E3").Select
    Selection.Copy
    Range("K17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D5:D10").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("K18").Select
End Sub

Final result in excel:

final result in excel

Solution

  • Accumulation of sequentially calculated values in a separate column

    Sub box_value()
    ' Find the lowest empty cell in column K and fill it with E3
    With Columns("K").Cells
        .Item(.Count).End(xlUp).Offset(1) = Range("E3").Value
    End With
    ' Clear a Quantity area
    Range("D5:D10").ClearContents
End Sub