Search:

# R1C1 Formula

Asked By: Kerri    Date: Sep 20    Category: MS Office    Views: 1866

How could I find the row # of "Total" - for example and use the row
number for calculation?

Below is my current code;
FinalRow = Cells(Application.Rows.Count, 3).End(xlUp).Row
FinalColumn = Cells(2, Application.Columns.Count).End
(xlToLeft).Column
' Luckyly, in my current WS, Total is located @ Final Row. What if
it's not located @ Final Row?

For C = 8 To FinalColumn + 15 Step 4
Cells(, C).Resize(, 2).EntireColumn.Insert Shift:=xlToRight
' It puzzle me why I have to add 15 to FinalColumn. I think I could
live w/ this.

' Start calculating % of Total from Row # 5
For R = 5 To FinalRow
Cells(R, C + 1).FormulaR1C1 = "=RC[-5]/R358C[-5]"
Next R
'R358 is hardcoded. How could I make it flexible? Something
like "R&FinalRow C[-5]"

C = C + 2

Next C

Share:

### 2 Answers Found

Answer #1    Answered By: Balbir Kaur     Answered On: Sep 20

I do not really understand the first 2 questions, but for the 3rd
quation you can use "concatenate"
So if you want to change 358 into variable, use this:

FormulaR1C1 = "=RC[-5]/R[" & any variable & "]C[-5]"

Answer #2    Answered By: Rene Sullivan     Answered On: Sep 20

> How could I find  the row  # of "Total" - for example  and use the row
> number for calculation?

If "Total" is sure to be present only once on the sheet:
Cells.Find ("Total").Row

> Below is my current  code;
> FinalRow = Cells(Application.Rows.Count, 3).End(xlUp).Row
> FinalColumn = Cells(2, Application.Columns.Count).End
> (xlToLeft).Column
> ' Luckyly, in my current WS, Total is located @ final  Row. What if
> it's not located @ Final Row?
>
> For C = 8 To FinalColumn + 15 Step 4
> Cells(, C).Resize(, 2).EntireColumn.Insert Shift:=xlToRight
> ' It puzzle  me why I have to add  15 to FinalColumn. I think I
could
> live w/ this.

Each time the loop is executed it adds 2 columns. Since you're working
from left to right the final column  of data increases by two each
time. You've hard coded the 15 which means you're probably working a
set number of columns  each time. Consider inserting the columns from
right to left, something like:
For C=Final column to 8 step -4
That way the column numbers you want to operate on won't change.

> ' Start calculating  % of Total from Row # 5
> For R = 5 To FinalRow
> Cells(R, C + 1).FormulaR1C1 = "=RC[-5]/R358C[-5]"
> Next R
> 'R358 is hardcoded. How could I make it flexible? Something
> like "R&FinalRow C[-5]"

Of course you can:
Cells(R, C + 1).FormulaR1C1 = "=RC[-5]/R" & FinalRow & "C[-5]"

Didn't find what you were looking for? Find more on R1C1 Formula Or get search suggestion and latest updates.