VBA Code

iangriffin83

Hi I'm looking for some help with a VBA code:

I have a list of values in cells A1 to A22, i want to insert a new cell below A1 and then paste the contents of A1 into the new cell repeating this inserting and pasting down to A22.

All help appreciated

srsly78

Which part do you have trouble with? Code below copies each cell into the cell below. If you just want A1 copied into all the cells below then that's an easy mod. Oh yeah and I transposed the rows and columns, oops.

Sub foo()
Dim rng As Range
Dim cell As Range

    Set rng = Range("A1:G1")
    For Each cell In rng
        Sheet1.Cells(cell.Row + 1, cell.Column) = cell.Value
    Next
End Sub

iangriffin83

Hi srsly78

Thanks for getting back to me, i tried the code and i cannot get it to work - it is copying 1 down along column A.

I have the below in cloumn A

1
2
3
4
5

and i need to have it as

1
1
2
2
3
3
4
4
5
5

srsly78

Try modifying the code above to do what you want, it's very easy. Just swap the columns and rows for starters. Then have two lines assigning cell values, with targetrow, and targetrow+1, with targetrow = (inputrow*2)-1. Just experiment until you get it right.

Oh yeah, I didn't bother inserting cells. I just put the stuff into a new row/column beside the original input.

namenotavailablE

Run this macro:

Sub FillUp()

For i = 2 To 44 'adjust the upper value to (2 * number of your last row) {22, in your example} 

    If ActiveSheet.Cells(i, 1).Row Mod 2 = 0 Then
    
        ActiveSheet.Cells(i, 1).Insert
        ActiveSheet.Cells(i, 1).Value = ActiveSheet.Cells(i - 1, 1).Value
    
    End If

Next

End Sub