lorraine
08-04-2008, 03:05 AM
hello,
I intend to copy the value in column J to column I as long as there is value in column A. An then I do a loop.
The macro below works just fine as long as I enter the value manually one after the other in column J, pretty boring...
But when I do a copy paste value in column J of an entire column, the macro does not work the same.
Indeed it copies the value of the column J only once in column I, just like if the loop was not working anymore. Can you help me? Thanks.
I have the code below :
Sub DoCopy()
Dim Rng As Range
Dim Cel As Range
Set Rng = Columns("J:J").SpecialCells(xlCellTypeConstants, 10)
For Each Cel In Rng
i = 0
Do
i = i + 1
If Cells(Cel.Row + i, 1) <> "" Then
Cells(Cel.Row + i, 9) = Cel.Value
Else
Exit Do
End If
Loop
Next
End Sub
I intend to copy the value in column J to column I as long as there is value in column A. An then I do a loop.
The macro below works just fine as long as I enter the value manually one after the other in column J, pretty boring...
But when I do a copy paste value in column J of an entire column, the macro does not work the same.
Indeed it copies the value of the column J only once in column I, just like if the loop was not working anymore. Can you help me? Thanks.
I have the code below :
Sub DoCopy()
Dim Rng As Range
Dim Cel As Range
Set Rng = Columns("J:J").SpecialCells(xlCellTypeConstants, 10)
For Each Cel In Rng
i = 0
Do
i = i + 1
If Cells(Cel.Row + i, 1) <> "" Then
Cells(Cel.Row + i, 9) = Cel.Value
Else
Exit Do
End If
Loop
Next
End Sub