Using Offset in a For Each loop

[Yeoman.jeremy]

Hi,
I currently have this:

Private Sub CalcPUnit_Click()Dim rCell As Range
Dim rRng As Range


Worksheets("Inventory").Select
Set rRng = Range("E3:E50")


For Each rCell In rRng
rCell.Value = rCell.Offset(, 2) / rCell.Offset(, 1)
Next rCell
  
End Sub

Basically to say each cell in a range needs to become the value of 2 cells to the left divided by 1 cell to the left.
Whenever I run this code I get an overflow error.
Any ideas?

Thanks

 

[James006]

Hi,

If your amounts are " to the left " ...

You need to adjust your Offsets : Offset(,-2) and Offset(,-1)

HTH

 

[Yeoman.jeremy]

Hi,

If your amounts are " to the left " ...

You need to adjust your Offsets : Offset(,-2) and Offset(,-1)

HTH

Hi, not correct
Also the problem wasn't with the directions it was something else thanks though

 

[James006]

Hi, not correct
Also the problem wasn't with the directions it was something else thanks though

Feel free ... not to stay mysterious ... about THE solution you have found ...

 

[Phuoc]

Try:

For Each rCell In rRng
If IsNumeric(rCell.Offset(, 2).Value) And rCell.Offset(, 1).Value <> 0 Then
rCell.Value = rCell.Offset(, 2) / rCell.Offset(, 1)
End If
Next rCell