- Joined
- Apr 6, 2006
- Messages
- 40
- Reaction score
- 1
I have a similar issue as several people in this sub-forum.
I have an integer stored in a variable that I want to insert into a R1C1 formula.
The Compiler for VBA has no problem with this, however, there is an error in run-time, Application or user defined error.
I am pretty sure there is a solution but don't know what it is.
Here is the code:
Option Explicit
Dim i As Integer
sub HrsSum()
ActiveCell.Offset(Int(i + 1), 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-(i)]C:R[-1]C)"
End Sub
The value of i is determined by a While loop.
I have tested and all works proporly exept last line, generating an error in run-time, Application or user defined error.
I have also tried puting the value of i into a cell and using a reference:
ActiveCell.FormulaR1C1 = "=SUM(R[(ActiveSheet.Range("I46").Value)]C:R[-1]C)"
But this generates a syntax error.
Note:
R1C1 formula works fine with a staight number. EX:
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-1]C)"
I would greatly appreciate any help.
Have a great day,
Smithb2
I have an integer stored in a variable that I want to insert into a R1C1 formula.
The Compiler for VBA has no problem with this, however, there is an error in run-time, Application or user defined error.
I am pretty sure there is a solution but don't know what it is.
Here is the code:
Option Explicit
Dim i As Integer
sub HrsSum()
ActiveCell.Offset(Int(i + 1), 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-(i)]C:R[-1]C)"
End Sub
The value of i is determined by a While loop.
I have tested and all works proporly exept last line, generating an error in run-time, Application or user defined error.
I have also tried puting the value of i into a cell and using a reference:
ActiveCell.FormulaR1C1 = "=SUM(R[(ActiveSheet.Range("I46").Value)]C:R[-1]C)"
But this generates a syntax error.
Note:
R1C1 formula works fine with a staight number. EX:
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-1]C)"
I would greatly appreciate any help.
Have a great day,
Smithb2