Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

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]"

> 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.

Related Topics:

- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- Quotations in Formulas and strings
- Disabling Formulas, Not calculation
- Blank Formula bar
- Manually creating a row outline with no formula anywhere
- sum formula
- cells containing formula that refer to user-defined VBA function
- Named formula/Windows dialog boxes
- Stuck on copying formula from 1 sheet to another
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- Array Formula
- Copy down formula with vba
- Loop in formula
- formula help but not vba please
- How to add several conditions for formula "SUMIF"
- combining formula
- VBA code to add values in two cells which have formulas
- VBA or array formula?
- Paste Formula help
- Three Leveled Formula
- Formula using range name in VBA code
- searching within formulas
- Populate Formula(Function) in rows
- Getting a formula to copy from inside VBA
- VBA Formula prob