Copy one value from one cell and past it into another cell with VBA

[Tormenta]

I am trying to find a way of doing the below with VBA code.

I have a list of numbers that goes from cell A1:A9 and I would like to paste the first value in cell I1, then have a loop that looks into the cell value (Cell A2) and past it 4 cells below I1 and so on. The tricky part is that if there is no number it will be replaced with a dash -, so the VBA code should read the - skip it and past the next value. If I have a number in cell A1 the code should copy and paste it in I1 if the next value in cell A2 is a dash - then it should skip it and check the value in cell A3 if its a number then copy and paste it cell I5. Also the range in A1:A9 is dynamic so it can change it can be A1:A100. How can I accomplish the above with VBA?

 

[Joe4]

Welcome to the Board!

Here is one way:

Sub MyCopyCells()

    Dim lr As Long
    Dim r As Long
    Dim ir As Long
   
    Application.ScreenUpdating = False
   
'   Initialize column I row variable
    ir = 1
   
'   Find last cell in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Populate column I
    For r = 1 To lr
        If Cells(r, "A") <> "-" Then
            Cells(ir, "I").Value = Cells(r, "A").Value
            ir = ir + 5
        End If
    Next r
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"

End Sub

 

[Tormenta]

This is fantastic, thank you a million.

 

[Joe4]

You are welcome.
Glad I was able to help!