Still need help

[erikkeith via OfficeKB.com]

Here is my problem:

Ok, two more things. 1. How can I do autofill formulas for an entire row
based off of finding the word "Totals"

After I put in this - Dim r As Range
Set r = Columns("a").Find("Totals", , , xlWhole)
If Not r Is Nothing Then r.Offset(, 1).FormulaR1C1 = "=sum(r1c:r[-1]c)" I
want to be able to run the formula for Column F11 and autofill all the way
down 2 rows up from the row that I have "Total" on.

I have names in column A, values in Columns B, C, D, E, G, H, J, K, M and O.
I need formulas in columns F, I, L and N. I have filled in my formulas for
the row that has the word "Totals"
in it. The first row with values in it is 11.

Please help!

 

[Bob Phillips]

Dim r As Range
Set r = Columns("A").Find("Totals", , , xlWhole)
If Not r Is Nothing Then
r.Offset(, 5).FormulaR1C1 = "=SUM(R11C6:R[-1]C6)"
r.Offset(, 8).FormulaR1C1 = "=SUM(R11C9:R[-1]C9)"
r.Offset(, 11).FormulaR1C1 = "=SUM(R11C12:R[-1]C12)"
r.Offset(, 13).FormulaR1C1 = "=SUM(R11C14:R[-1]C14)"
End If


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[erikkeith via OfficeKB.com]

Bob what I am looking for is something like this:

A B C D
E F
11 name 64 327 45 85
autofill
12 name 58 43 94 57
autofill
13 name 47 589 89 35
autofill
..
..
..
98
=E#/B#

100 Totals formula

I have figured out how to get the formula in column C with this:

Dim r As Range
Set r = Columns("a").Find("Totals", , , xlWhole)
If Not r Is Nothing Then r.Offset(, 2).FormulaR1C1 = "=sum(r11c:r[-1]c)"

What I need now is to figure out how to create the formula in F98 and then
have it autofill all the way up to F11

Dim r As Range
Set r = Columns("A").Find("Totals", , , xlWhole)
If Not r Is Nothing Then
r.Offset(, 5).FormulaR1C1 = "=SUM(R11C6:R[-1]C6)"
r.Offset(, 8).FormulaR1C1 = "=SUM(R11C9:R[-1]C9)"
r.Offset(, 11).FormulaR1C1 = "=SUM(R11C12:R[-1]C12)"
r.Offset(, 13).FormulaR1C1 = "=SUM(R11C14:R[-1]C14)"
End If

Here is my problem:

[quoted text clipped - 13 lines]

Please help!

 

[Bob Phillips]

I don't understand what you mean by autofill from F98 upto F11. Why would
you want that? And where is your formula in column C going (which row)?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

Bob what I am looking for is something like this:

A B C D
E F
11 name 64 327 45 85
autofill
12 name 58 43 94 57
autofill
13 name 47 589 89 35
autofill
.
.
.
98
=E#/B#

100 Totals formula

I have figured out how to get the formula in column C with this:

Dim r As Range
Set r = Columns("a").Find("Totals", , , xlWhole)
If Not r Is Nothing Then r.Offset(, 2).FormulaR1C1 = "=sum(r11c:r[-1]c)"

What I need now is to figure out how to create the formula in F98 and then
have it autofill all the way up to F11

Dim r As Range
Set r = Columns("A").Find("Totals", , , xlWhole)
If Not r Is Nothing Then
r.Offset(, 5).FormulaR1C1 = "=SUM(R11C6:R[-1]C6)"
r.Offset(, 8).FormulaR1C1 = "=SUM(R11C9:R[-1]C9)"
r.Offset(, 11).FormulaR1C1 = "=SUM(R11C12:R[-1]C12)"
r.Offset(, 13).FormulaR1C1 = "=SUM(R11C14:R[-1]C14)"
End If

Here is my problem:

[quoted text clipped - 13 lines]

Please help!

 

[erikkeith via OfficeKB.com]

F11 is where my row starts with values. The # of names in the sheet is
subject to change so the row that "Totals" is on will/might change. F11 will
always be constant so since I have to search/find the page for "Totals" and
then populate column C (in this example C:100) with the formula =(C11:C99) I
then have my second point of reference to move over to cell F:98 - r.Offset(-
2, 5) and enter in the formula =(E98/B98). Here is where I get stuck because
I want to autofill the formula in F98 all the way up to F11. Can you help?

I don't understand what you mean by autofill from F98 upto F11. Why would
you want that? And where is your formula in column C going (which row)?

Bob what I am looking for is something like this:

[quoted text clipped - 37 lines]

 

[Bob Phillips]

Try this

Dim r As Range
Set r = Columns("A").Find("Totals", , , xlWhole)
If Not r Is Nothing Then
r.Offset(, 2).FormulaR1C1 = "=SUM(R11C:R[-1]C)"
Range("F11").FormulaR1C1 = "=IF(RC[-4]<>0,RC[-1]/RC[-4],"""")"
Range("F11").AutoFill Range("F11:F" & r.Row - 1)
End If


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

F11 is where my row starts with values. The # of names in the sheet is
subject to change so the row that "Totals" is on will/might change. F11 will
always be constant so since I have to search/find the page for "Totals" and
then populate column C (in this example C:100) with the formula =(C11:C99) I
then have my second point of reference to move over to cell F:98 - r.Offset(-
2, 5) and enter in the formula =(E98/B98). Here is where I get stuck because
I want to autofill the formula in F98 all the way up to F11. Can you help?

I don't understand what you mean by autofill from F98 upto F11. Why would
you want that? And where is your formula in column C going (which row)?

Bob what I am looking for is something like this:

[quoted text clipped - 37 lines]

Please help!

 

[erikkeith via OfficeKB.com]

I think that works! Thanks

Try this

Dim r As Range
Set r = Columns("A").Find("Totals", , , xlWhole)
If Not r Is Nothing Then
r.Offset(, 2).FormulaR1C1 = "=SUM(R11C:R[-1]C)"
Range("F11").FormulaR1C1 = "=IF(RC[-4]<>0,RC[-1]/RC[-4],"""")"
Range("F11").AutoFill Range("F11:F" & r.Row - 1)
End If

F11 is where my row starts with values. The # of names in the sheet is
subject to change so the row that "Totals" is on will/might change. F11 will

[quoted text clipped - 12 lines]