Find empty cell and input data for other cells
 

Hi,
I want to move (cut paste?) cell content from columns D:F to the left by
one position (offset?). When cell C is empty. This test I am trying is setup
for
only 4 rows d2:d5 (hear in rown 1), but the real work will have 2300 rows
involved. This is
a situation where data coming is to us is not formated correctly (i.e.)
city,state,zip
which should be situated in cells C,D & E is on some rows located in D,E & F

Sub OffsetRange()
Dim r As Range
Dim myVar
Dim myCell As Range
Set myCell = Range("C2:c5")
Dim c As Range
Set c = Range("D:F")
For Each r In Range("C2:C5" & Cells(Rows.Count, "c").End(xlUp).Row)
myVar = IsEmpty(r.Value)
If myVar = True Then
?????? I'm lost here

End If
Next
End Sub

Any help appreciated
Rick

Find empty cell and input data for other cells
 

Hey, Rick. I'd do it like this:

Public Sub OffsetRange()
cells(2,1).select '"A2"

do until activecell = "" 'til last row of the column A
if activecell.offset(0,2)="" Then 'C is blank
activecell.offset(0,2)=activecell.offset(0,3)
activecell.offset(0,3)=activecell.offset(0,4)
activecell.offset(0,4)=activecell.offset(0,5)
end if
End Sub

You didn't say if there was anything in columns G, H, etc.....

Chris

On Mar 11, 9:22*am, Rick K wrote:
Hi,
* I want to move (cut paste?) cell content from columns D:F to the left by
one position (offset?). When cell C is empty. This test I am trying is setup
for
only 4 rows d2:d5 (hear in rown 1), but the real work will have 2300 rows
involved. This is
a situation where data coming is to us is not formated correctly (i.e.)
city,state,zip
which should be situated in cells C,D & E is on some rows located in D,E & F

Sub OffsetRange()
Dim r As Range
Dim myVar
Dim myCell As Range
* * Set myCell = Range("C2:c5")
Dim c As Range
* * Set c = Range("D:F")
* * For Each r In Range("C2:C5" & Cells(Rows.Count, "c").End(xlUp).Row)
* * *myVar = IsEmpty(r.Value)
* * * * If myVar = True Then
* * * * ?????? I'm lost here

* * * * End If
* * Next
End Sub

Any help appreciated
Rick

Find empty cell and input data for other cells
 

Give this macro a try...

Sub MoveBadRanges()
Dim R As Range
With Worksheets("Sheet2")
For Each R In .Range("C1:C" & .UsedRange.Rows.Count).Cells. _
SpecialCells(xlCellTypeBlanks)
R.Offset(0, 1).Resize(1, 3).Copy R
R.Offset(0, 3).Clear
Next
End With
End Sub

Rick


"Rick K" wrote in message
...
Hi,
I want to move (cut paste?) cell content from columns D:F to the left by
one position (offset?). When cell C is empty. This test I am trying is
setup
for
only 4 rows d2:d5 (hear in rown 1), but the real work will have 2300 rows
involved. This is
a situation where data coming is to us is not formated correctly (i.e.)
city,state,zip
which should be situated in cells C,D & E is on some rows located in D,E &
F

Sub OffsetRange()
Dim r As Range
Dim myVar
Dim myCell As Range
Set myCell = Range("C2:c5")
Dim c As Range
Set c = Range("D:F")
For Each r In Range("C2:C5" & Cells(Rows.Count, "c").End(xlUp).Row)
myVar = IsEmpty(r.Value)
If myVar = True Then
?????? I'm lost here

End If
Next
End Sub

Any help appreciated
Rick