Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable in Excel formula in VBA statement
Excel 2000 VBA using R1C1 notation in the Workbook
I've written a simple "Journal" (check register) in a worksheet, and I'm trying to program split checks. On the line below the individual items in the split, in a different column, I want to total all of the individual values in the split (state a total value for the check). I want to generate the formula to do that with a VBA statement. The following code runs OK and does *almost* what I want to do: ActiveCell.Offset(i1, 3).FormulaR1C1 = "=""Total of check is $ ""&FIXED(SUM(R[-4]C[3]:R[-1]C[3]),2)" What I really need is for the "4" in "R[-4]" to not be a hard coded number, but to be obtained from the value of an integer variable. The name of the variable I'm using is "i1", and although it's a cell reference in "normal" notation, it seems to work fine in all of the other code in my macro. It is declared in a Dim statement: Dim i1 as Integer It works fine in the "Offset(i1, 3)" part of the above formula. Simply substituting "-i1" for "-4" does not work. I've guessed at some variations which also do not work. When the code fails, I get no runtime error in the macro, but the designated cell is (apparently) empty; no formula at all is placed in the desingated cell. Thanks for any help Fred Holmes |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable statement | Excel Discussion (Misc queries) | |||
IF Statement Variable | Excel Discussion (Misc queries) | |||
Variable IF statement (in VBA) | Excel Programming | |||
how can i calculate a three variable if statement in excel | Excel Worksheet Functions | |||
Macro Creating Variable and using variable in a SQL statement | Excel Programming |