1

I have made this very simple loop to apply a formula to all cells in a range:

For Each cell In Range("G5:G500")
    cell.Offset(0, 7).FormulaR1C1 = "=IF(Len(RC[-4]>0),(RC[-2])/(RC[-4]),"")"
Next

It gives "application-defined or object-defined error" for the line

cell.Offset(0, 7).FormulaR1C1 = "=IF(Len(RC[-4]>0),(RC[-2])/(RC[-4]),"")"

I can't work out where i'm going wrong here. I've tried:

  • Various different formats of R1C1 (e.g RC[1] or R[]C[1])
  • using formula instead of R1C1 but then I can't refer to cells geometrically
  • I'm pretty sure all my bracketing is correct

very baffled and would really appreciate a tip or hint on this!

IIJHFII
  • 600
  • 1
  • 7
  • 23
  • I think the IF formula needs an ending paranthesis:`cell.Offset(0, 7).FormulaR1C1 = "=IF(Len(RC[-4]>0),(RC[-2])/(RC[-4]),"")"` – SrinR Apr 14 '16 at 09:14
  • 1
    See http://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba. You have quotation marks in a string. So `.FormulaR1C1 = "=IF(Len(RC[-4]>0),(RC[-2])/(RC[-4]),"""")"`will work. – Axel Richter Apr 14 '16 at 09:59
  • `.FormulaR1C1 = "=IF(Len(RC[-4])>0,RC[-2]/RC[-4],"""")"` will better suit what you probably want. – Axel Richter Apr 14 '16 at 10:13

2 Answers2

0

Not sure where the issue is, but when i change your code to 

For Each cell In Range("G5:G500")
    cell.Offset(0, 7).FormulaR1C1 = "=IF(Len(RC[-4]>0),(RC[-2])/(RC[-4]),N/A)"
Next

it works fine. So probably issue in if statement with "" inserted in cell... (i will make a deeper look)

edit: yes, issue with was bracket as someone mentioned in comment

For Each cell In Range("G5:G500")
    cell.Offset(0, 7).FormulaR1C1 = "=IF(Len(RC[-4]>0),(RC[-2])/(RC[-4]),)"
Next
Luboš Suk
  • 1,526
  • 14
  • 38
0

I've had this before! Change 0 to 0.001 or something small and wrap it in an IFERROR formula to stop errors that come from having vba do the formula! and that should do the trick

Sedss
  • 159
  • 5