Absolute Cell Values in VB Code 1

[StayAtHomeMom]

I am using VB code behind a button on a form in Access to create an Excel file (from a query) and format it. Part of the formatting is to place formulas in a column, using cell contents from another column. For instance, starting with cell N2, the formula should read “=L2/$L$15”; cell N3 would read “=L3/$L$15”, and so on. The problem is with the absolute values I’m using. I can’t figure out the proper syntax when using the R1C1 notation within the code. The other complexity is that I’m using this code to create separate sheets within the Excel file, and each sheet will have a different row value for the totals line I’m using in the formula (the denominator). I thought by using a variable to hold the row value that’s holding the total line (what I call “totrow”), I would lock in the value of the row within the formula. But, Excel increments the row value, so I end up with a lot of #DIV/0# errors.

Can someone shed some light on the proper syntax to imbed an absolute cell value within an R1C1 formula (see ---> below)? I’ve pasted a portion of the code below, where I’m calculating the totals for certain columns, and using those totals in the formula I’m trying to come up with.

rowcount = 2
Do While (Cells(rowcount, 1).Value <> "") 'figure out the total number of records/rows for this disty
rowcount = rowcount + 1
Loop

'add totals line for col 5 thru 12
Cells(rowcount, 1).Select
ActiveCell.FormulaR1C1 = "TOTALS"
For numbcol = 5 To 12
Cells(rowcount, numbcol).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-" & rowcount - 1 & "]C)" 'totaling Year and YTD columns
Next numbcol

'compute Market Share; i.e. SalesInTerr/TotalSales
totrow = rowcount - 2 'totrow will be row with cumulative total for the column
rowcount = 2
Cells(rowcount, 14).Select
Do While (Cells(rowcount, 1).Value <> "")
---> ActiveCell.FormulaR1C1 = "=RC[-2]/$R[" & totrow & "]$C[-2]"
rowcount = rowcount + 1
Cells(rowcount, 14).Select
Loop

THANKS IN ADVANCE FOR YOUR EXPERTISE!

 

[TonyJollans]

Hi StayAtHomeMom,

You don't need to use the dollar symbols to make an R1C1 address absolute, thus:

.FormulaR1C1 = "=RC[-2]/R[" & totrow & "]C[-2]"

Absolute row and column numbers are expressed with numbers alone; relative ones as offsets in (square) brackets.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[TonyJollans]

Oops, Sorry! That should be:

.FormulaR1C1 = "=RC[-2]/R" & totrow & "C[-2]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[TonyJollans]

Also, you don't need to Select cells in order to work with them; it's generally better not to, so:

            'compute Market Share; i.e. SalesInTerr/TotalSales
            totrow = rowcount - 2   'totrow will be row with cumulative total for the column
            rowcount = 2
            Cells(rowcount, 14).Select
            Do While (Cells(rowcount, 1).Value <> "")
                ActiveCell.FormulaR1C1 = "=RC[-2]/$R[" & totrow & "]$C[-2]"
                rowcount = rowcount + 1
                Cells(rowcount, 14).Select
            Loop

could become just:

[blue]            [green]'compute Market Share; i.e. SalesInTerr/TotalSales[/green]
            totrow = rowcount - 2   [green]'totrow will be row with cumulative total for the column[/green]
            rowcount = 2
            Do While (Cells(rowcount, 1).Value <> "")
                Cells(rowcount, 14).FormulaR1C1 = "=RC[-2]/R" & totrow & "C[-2]"
                rowcount = rowcount + 1
            Loop[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[xlbo]

To be honest - no need to loop either - or use R1C1 notation - just enter the formula with the appropriate $ signs and excel will do the rest - try the following to see what I mean:

Range("D1:E10").formula = "=SUM($A1:B1)"

In D1, the formula will be =SUM($A1:B1)
In D2, the formula will be =SUM($A2:B2)
In E1, the formula will be =SUM($A1:C1)
In E2, the formula will be =SUM($A2:C2)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question