Offset Error

[jfcby]

I'm using Excel 2003.

With the folleowin code I'm getting the object global failed error at
this line in the code: Range(rw, 4).Value = Range(rw, 4).Offset(0,
-1).Value.

What needs to be chaned in that line so that the code will work?

Sub CopyCellValueExample1()
'If the Column D selection has blank cells then copy data from column
C _
same cell.

Dim rngR As Object, rw As Variant
For Each rngR In Selection
rw = rngR.row
If rngR = "" Then _
Range(rw, 4).Value = Range(rw, 4).Offset(0, -1).Value
Next rngR
End Sub

Thank you for your help,
jfcby

 

[Don Guillett]

try

mc=3 'column c to look for the last row in the filled in column
lr=cells(rows.count,mc).end(xlup).row
For Each c In Range(cells(2,mc),cells(lr,mc))
If c.offset(,1) = "" Then _
Range(c, mc+1).Value = Range(c,mc).Value
Next c

 

[Joel]

do yo have any merged cells? If the cell in column C is merged with column B
there will be an error.

 

[Rick Rothstein \(MVP - VB\)]

If I understand what you are doing correctly, I think you may want to use
this...

rngR.Value = rngR.Offset(0, -1).Value

instead of this...

Range(rw, 4).Value = Range(rw, 4).Offset(0, -1).Value

Rick

 

[jfcby]

try

mc=3     'column c to look for the last row in the filled in column
lr=cells(rows.count,mc).end(xlup).row
 For Each c In Range(cells(2,mc),cells(lr,mc))
     If c.offset(,1) = "" Then _
     Range(c, mc+1).Value = Range(c,mc).Value
Next c

--
Don Guillett
Microsoft MVP Excel
SalesAid Software










- Show quoted text -

I'm still getting the same error as above.

I'm manually selecting cells in column D then if cell is empty then it
will copy data from column c to column d.

Thank you for your help,
jfcby

 

[jfcby]

do yo have any merged cells?  If the cell in column C is merged with column B
there will be an error.










- Show quoted text -

No, I do not have merged cells.

Thank you for help,
jfcby

 

[jfcby]

If I understand what you are doing correctly, I think you may want to use
this...

rngR.Value = rngR.Offset(0, -1).Value

instead of this...

Range(rw, 4).Value = Range(rw, 4).Offset(0, -1).Value

Rick











- Show quoted text -

Worked great! Thanks! Been tring to figure that out for three hours!
jfcby

 

[Don Guillett]

My macro looked in col C since it was the column with the filled in cells.
Then, for each cell in col D that was blank it filled in the data from col
c. Selections were not needed.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

try

mc=3 'column c to look for the last row in the filled in column
lr=cells(rows.count,mc).end(xlup).row
For Each c In Range(cells(2,mc),cells(lr,mc))
If c.offset(,1) = "" Then _
Range(c, mc+1).Value = Range(c,mc).Value
Next c

--
Don Guillett
Microsoft MVP Excel
SalesAid Software










- Show quoted text -

I'm still getting the same error as above.

I'm manually selecting cells in column D then if cell is empty then it
will copy data from column c to column d.

Thank you for your help,
jfcby