Loop Statement through If Not IsEmpty Then Paste into Destination

[Dandelo]

Trying to write macro that will loop through cells in Column"O" and If Not
IsEmpty paste the Value in Column"O" into Column"E" of the same row number.
Any thoughts? Any help is greatly appreciated.

I have some examples I've picked up from the MVPs in this group, but I can't
seem to put it together. (I've also looked at Ron DeBruin's site which is
excellent, but I still need help).
I use variations of these in current macros (but I can't remember which
posts I got these from to give the author credit):

Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("A2").Select
Selection.AutoFill _
Destination:=Range("A2:A" & LastRow), Type:=xlFillDefault
End With

Dim i As Integer

i = 2
For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "I").Value = "" Then
Cells(i, "I").Value = Cells(i - 1, "I").Value
End If
Next i

 

[Mike H]

Hi,

Right click the sheet tab, view code and paste this and try it

Sub copyit()
Dim myrange As Range
lastrow = Cells(Rows.Count, "O").End(xlUp).Row
Set myrange = Range("O1:O" & lastrow)
For Each c In myrange
If Not IsEmpty(c) Then
c.Offset(, -10).Value = c.Value
End If
Next
End Sub

Mike

 

[Dandelo]

Excellent. Will I set c as a dimension? like c As long?

 

[JLatham]

There's a difference between being empty and having a zero length string ( ""
) in a cell. An empty cell truly has nothing in it - no value, no formula.

I would do this with your loop, beginning with the Dim i As Integer statement

Dim colORange As Range
Dim anyColOCell As Range

Set colORange = ActiveSheet.Range("O2:" & _
ActiveSheet.Range("O" & Rows.Count).End(xlUP).Address)
For Each anyColOCell In colORange
If Not IsEmpty(anyColOCell) Then
'copy from column O to column E (-10 columns)
anyColOCell.Offset(0, -10) = anyColOCell
End If
Next
Set colORange = Nothing

 

[Dandelo]

Hot digity dog!, Mike it worked! Here's what I ended up with: I set LastRow
as Long and c As variant. thank you so much!!!

Sub copyit()

Dim LastRow As Long
Dim myrange As Range
Dim c As Variant

LastRow = Cells(Rows.Count, "O").End(xlUp).Row
Set myrange = Range("O1:O" & LastRow)
For Each c In myrange
If Not IsEmpty(c) Then
c.Offset(, -10).Value = c.Value
End If
Next
End Sub

 

[Dandelo]

Thank you, JLatham. Here's what I entered into the tab and it worked!

Sub LathamCopyit()

Dim colORange As Range
Dim anyColOCell As Range

Set colORange = ActiveSheet.Range("O2:" & _
ActiveSheet.Range("O" & Rows.Count).End(xlUp).Address)
For Each anyColOCell In colORange
If Not IsEmpty(anyColOCell) Then
'copy from column O to column E (-10 columns)
anyColOCell.Offset(0, -10) = anyColOCell
End If
Next
Set colORange = Nothing
End Sub

 

[Mike H]

Hi,

Well spotted that I'm lazy and should have dimensioned C as range. I'm glad
that helped.

Mike

 

[JLatham]

I see that Mike H gave you a nice solution also. Thanks for the feedback.
Good luck with your project.