If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Loop Statement through If Not IsEmpty Then Paste into Destination
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 -- Thank you so much for your help! |
#2
|
|||
|
|||
Loop Statement through If Not IsEmpty Then Paste into Destination
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" wrote: 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 -- Thank you so much for your help! |
#3
|
|||
|
|||
Loop Statement through If Not IsEmpty Then Paste into Destinat
Excellent. Will I set c as a dimension? like c As long?
-- Thank you so much for your help! "Mike H" wrote: 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" wrote: 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 -- Thank you so much for your help! |
#4
|
|||
|
|||
Loop Statement through If Not IsEmpty Then Paste into Destination
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" wrote: 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 -- Thank you so much for your help! |
#5
|
|||
|
|||
Loop Statement through If Not IsEmpty Then Paste into Destinat
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 -- Thank you so much for your help! "Mike H" wrote: 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" wrote: 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 -- Thank you so much for your help! |
#6
|
|||
|
|||
Loop Statement through If Not IsEmpty Then Paste into Destinat
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 -- Thank you so much for your help! "JLatham" wrote: 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" wrote: 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 -- Thank you so much for your help! |
#7
|
|||
|
|||
Loop Statement through If Not IsEmpty Then Paste into Destinat
Hi,
Well spotted that I'm lazy and should have dimensioned C as range. I'm glad that helped. Mike "Dandelo" wrote: 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 -- Thank you so much for your help! "Mike H" wrote: 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" wrote: 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 -- Thank you so much for your help! |
#8
|
|||
|
|||
Loop Statement through If Not IsEmpty Then Paste into Destinat
I see that Mike H gave you a nice solution also. Thanks for the feedback.
Good luck with your project. "Dandelo" wrote: 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 -- Thank you so much for your help! "JLatham" wrote: 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" wrote: 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 -- Thank you so much for your help! |
Thread Tools | |
Display Modes | |
|
|