Int Variable into R1C! Formula

[smithb2]

I have a similar issue as Dev, in this same Newsgroup.

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

 

[smithb2]

Solution Found

By help of the group, I have found a solution.

As you may recall, as with your situation, I was trying to automatically determine, with a macro, how many columns were to be added and insert this info into the sum formula:

Option Explicit
Dim i As Integer

Sub HrsTotal()
i = 0

‘Do loop determines # of columns to store in integer variable (Hrs is the header for the ‘column

Do While ActiveCell.Value <> "Hrs"

ActiveCell.Offset(-1, 0).Select

i = i + 1

Loop

‘Following line uses the # stored in “i” to select cell in which to place sum function

ActiveCell.Offset(Int(i), 0).Select

‘In the following, I simply turn the value stored in “i” from positive to negative, in ‘preparation for sum formula.

i = -(i)

ActiveCell.FormulaR1C1 = "=SUM(R[" & i & "]C:R[-1]C)"

End Sub


The last line contains what I was unable to figure out, How to get the sum formula to accept the variable "i" as a value: " & i & "


Thank you.

Have a great day,
Smithb2