Ok, here is my objective. I have names in column A and totals in column B.
At the bottom of the excel sheet I have a cell titled "Totals". I want the
macro to be able to find the word totals, create borders for the entire sheet
and the run formulas in the "Totals" row for column B. I want the macro to
search out the first name in column A so the formulas for the Totals has
everything from column B. Hope that isn't confusing. Here is a diagram:

Column A Column B

name 98
name 97
name 89
name 64

Totals formula =SUM(B#:B#)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

Hi
Is it something like this?

Code:
--------------------

Sub test()
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)"
End Sub

--------------------

erikkeith via OfficeKB.com Wrote:
Ok, here is my objective. I have names in column A and totals in column
B.
At the bottom of the excel sheet I have a cell titled "Totals". I want
the
macro to be able to find the word totals, create borders for the entire
sheet
and the run formulas in the "Totals" row for column B. I want the
macro to
search out the first name in column A so the formulas for the Totals
has
everything from column B. Hope that isn't confusing. Here is a
diagram:

Column A Column B

name 98
name 97
name 89
name 64

Totals formula =SUM(B#:B#)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=540544

Yes, but what if I want to have another formula for column C on that same row
in addition to column B?

jindon wrote:
Hi
Is it something like this?

Code:
--------------------

Sub test()
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)"
End Sub

--------------------

erikkeith via OfficeKB.com Wrote:
Ok, here is my objective. I have names in column A and totals in column
B.
[quoted text clipped - 17 lines]

Totals formula =SUM(B#:B#)


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

--------------------------------------------------------------------------------

Yes, but what if I want to have another formula for column C on that
same row
in addition to column B?


Tell us what you are really after then

I can't see inside of your brain....


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=540544

Can you help me with my latest question?

jindon wrote:
--------------------------------------------------------------------------------

Yes, but what if I want to have another formula for column C on that
same row
in addition to column B?

Tell us what you are really after then

I can't see inside of your brain....


--
Message posted via http://www.officekb.com

have you tried the code?

Code:
--------------------

Sub test()
Dim r As Range
Set r = Columns("a").Find("Totals", , , xlWhole)
If Not r Is Nothing Then _
r.Offset(, 1).Resize(, 3).FormulaR1C1 = "=sum(r2c:r[-2]c)"
With Range("a2", Range("a" & Rows.Count).End(xlUp))
.Offset(, 3).FormulaR1C1 = "=sum(rc[-2]:rc[-1])"
End With
End Sub
--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=540544

Nevermind that one. I figured it out. I still have another question so give
me a sec k? I am trying to figure it out.

jindon wrote:
Hi
Is it something like this?

Code:
--------------------

Sub test()
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)"
End Sub

--------------------

erikkeith via OfficeKB.com Wrote:
Ok, here is my objective. I have names in column A and totals in column
B.
[quoted text clipped - 17 lines]

Totals formula =SUM(B#:B#)


--
Message posted via http://www.officekb.com

Ok, two more things. 1. How can I do autofill formulas for an entire row
based off what we just did? So in otherwords using the following:

Column A Column B Column C Column D

name 98 97 =Sum(B#:C#)
name 97 96 autofill
name 89 82 autofill
name 64 78 autofill

Totals formula =SUM(B#:B#)

After I put in what you gave me - 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 C11 and autofill all the way
down 2 rows up from the row that I have "Total" on.

jindon wrote:
Hi
Is it something like this?

Code:
--------------------

Sub test()
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)"
End Sub

--------------------

erikkeith via OfficeKB.com Wrote:
Ok, here is my objective. I have names in column A and totals in column
B.
[quoted text clipped - 17 lines]

Totals formula =SUM(B#:B#)


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

OK
no need to fildown

Code:
--------------------

Sub test()
Dim r As Range
Set r = Columns("a").Find("Totals", , , xlWhole)
If Not r Is Nothing Then _
r.Offset(, 1).resize(,2).FormulaR1C1 = "=sum(r2c:r[-1]c)"
with range("a2", range("a" & rows.count).end(xlup))
.offset(,3).formular1c1="=sum(rc[-2]:rc[-1])"
end with
End Sub
--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=540544

Ok I give up. I don't understand exactly what this command does and what it
manipulates so I will just give you my true case:

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 based off of what you have given me
so far. 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.

jindon wrote:
OK
no need to fildown

Code:
--------------------

Sub test()
Dim r As Range
Set r = Columns("a").Find("Totals", , , xlWhole)
If Not r Is Nothing Then _
r.Offset(, 1).resize(,2).FormulaR1C1 = "=sum(r2c:r[-1]c)"
with range("a2", range("a" & rows.count).end(xlup))
.offset(,3).formular1c1="=sum(rc[-2]:rc[-1])"
end with
End Sub
--------------------


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1