PDA

View Full Version : VBA error 1004- Glitch or Coding Error?!



Kramerica44
06-21-2013, 12:33 PM
Hello All,

The code below yields run-time error 1004 on the lines for AH15 and AH44, but works fine for AH19 and AH48. The only change to the formulas in AH15 and AH44 was "-R[-5]C[-20]-R[-5]C[-19]-R[-5]C[-11]" replaced "-R[-5]C[-21]-R[-5]C[-12]". The macro uses an input box for the week number and then clears all relevant cells before entering any formulas. I see no reason that my code should break based on the changes I've made. Is there anything in the formatting of the cells or something similar that could cause my code to fail? Otherwise, is this a possible glitch and if so how can I remedy it? I tried re-saving and copying and pasting the data and then pasting in the updated VBA code but neither approach solved the problem.

Thanks in advance for any and all attempts to help!

The code:

If Week = 10 Then
Range("AH15").Select
ActiveCell.FormulaR1C1 = _
"=RC[-21]+RC[-12]+sum(R[-4]C[-31]:RC[-31])-R[-5]C[-20]-R[-5]C[-19]-R[-5]C[-11]-RC[-10]-RC[-9]"

Range("AH19").Select
ActiveCell.FormulaR1C1 = _
"=RC[-21]+RC[-12]+sum(R[-3]C[-31]:RC[-31])-R[-4]C[-21]-R[-4]C[-12]-RC[-10]-RC[-9]"

....

Range("AH44").Select
ActiveCell.FormulaR1C1 = _
"=RC[-21]+RC[-12]+sum(R[-4]C[-31]:RC[-31])-R[-5]C[-20]-R[-5]C[-19]-R[-5]C[-11]-RC[-10]-RC[-9]"

Range("AH48").Select
ActiveCell.FormulaR1C1 = _
"=RC[-21]+RC[-12]+sum(R[-3]C[-31]:RC[-31])-R[-4]C[-21]-R[-4]C[-12]-RC[-10]-RC[-9]"
....

End If

SamT
06-21-2013, 12:52 PM
I noticed that the two formulae that give errors refer to the two following formulae.
Try this:
Application.Calculation = xlCalculationManual
Application ScreenUpdating = False

If Week = 10 Then
Range("AH19").FormulaR1C1 = _
"=RC[-21]+RC[-12]+sum(R[-3]C[-31]:RC[-31])-R[-4]C[-21]-R[-4]C[-12]-RC[-10]-RC[-9]"

Range("AH15").FormulaR1C1 = _
"=RC[-21]+RC[-12]+sum(R[-4]C[-31]:RC[-31])-R[-5]C[-20]-R[-5]C[-19]-R[-5]C[-11]-RC[-10]-RC[-9]"


....

Range("AH48").FormulaR1C1 = _
"=RC[-21]+RC[-12]+sum(R[-3]C[-31]:RC[-31])-R[-4]C[-21]-R[-4]C[-12]-RC[-10]-RC[-9]"

Range("AH44").FormulaR1C1 = _
"=RC[-21]+RC[-12]+sum(R[-4]C[-31]:RC[-31])-R[-5]C[-20]-R[-5]C[-19]-R[-5]C[-11]-RC[-10]-RC[-9]"

....
Application ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic