macro to sub total a unique cell value
 

Help please!! is is possible to have macro that sub total
G1:G498 and put the answer infront of a cell
that has vacation1. example if vacation1 is in A500 i
want the subtotal at B500. And the cell below vacation1
will be
sick1 and i will like to sub total H1:H498 and have the
answer in the cell in front of sick1. Example if sick1 is
in
A501 I want the subtotal at B501. I do not want to use
the cell number because it changes. only the name is
unique for each totals. And then for G498:G598 I will
like the
answer in vacation1 B600. AND so on. This project is too
long and I am not familiar with macros.
Thanks all in advance

macro to sub total a unique cell value
 

Dim rng as Range
set rng = cells.Find("vacation1", ... other args)
if not rng is nothing then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End if

See help for the arguments to FIND or turn on the macro recorder and do
Edit=Find manually.

Use similar code for Sick1.
--
Regards,
Tom Ogilvy


mary wrote in message
...
Help please!! is is possible to have macro that sub total
G1:G498 and put the answer infront of a cell
that has vacation1. example if vacation1 is in A500 i
want the subtotal at B500. And the cell below vacation1
will be
sick1 and i will like to sub total H1:H498 and have the
answer in the cell in front of sick1. Example if sick1 is
in
A501 I want the subtotal at B501. I do not want to use
the cell number because it changes. only the name is
unique for each totals. And then for G498:G598 I will
like the
answer in vacation1 B600. AND so on. This project is too
long and I am not familiar with macros.
Thanks all in advance

Tom.. macro to sub total a unique cell value
 

Thanks a million Tom. I insect and paste this macro and
the statements are red. Meaning that something wrong.
I am new to macro. could you tell me what i need to
do before copying and pasting this macro. i thought i
could simply call this macro within a recorded macro?
thanks again
-----Original Message-----
Dim rng as Range
set rng = cells.Find("vacation1", ... other args)
if not rng is nothing then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End if

See help for the arguments to FIND or turn on the macro
recorder and do
Edit=Find manually.

Use similar code for Sick1.
--
Regards,
Tom Ogilvy


mary wrote in message
...
Help please!! is is possible to have macro that sub
total
G1:G498 and put the answer infront of a cell
that has vacation1. example if vacation1 is in A500 i
want the subtotal at B500. And the cell below vacation1
will be
sick1 and i will like to sub total H1:H498 and have the
answer in the cell in front of sick1. Example if sick1
is
in
A501 I want the subtotal at B501. I do not want to use
the cell number because it changes. only the name is
unique for each totals. And then for G498:G598 I will
like the
answer in vacation1 B600. AND so on. This project is too
long and I am not familiar with macros.
Thanks all in advance



.

TOM macro to sub total a unique cell value
 

Also i am getting syntax error for statement:
rng.offset(1,0).Formula = "=SUM(G1:G498")

This is how i pasted your formula

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End If
End Sub

Could you please please tell me what i am doing wrong.
I greatly appreciate your help.
-----Original Message-----
Dim rng as Range
set rng = cells.Find("vacation1", ... other args)
if not rng is nothing then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End if

See help for the arguments to FIND or turn on the macro
recorder and do
Edit=Find manually.

Use similar code for Sick1.
--
Regards,
Tom Ogilvy


mary wrote in message
...
Help please!! is is possible to have macro that sub
total
G1:G498 and put the answer infront of a cell
that has vacation1. example if vacation1 is in A500 i
want the subtotal at B500. And the cell below vacation1
will be
sick1 and i will like to sub total H1:H498 and have the
answer in the cell in front of sick1. Example if sick1
is
in
A501 I want the subtotal at B501. I do not want to use
the cell number because it changes. only the name is
unique for each totals. And then for G498:G598 I will
like the
answer in vacation1 B600. AND so on. This project is too
long and I am not familiar with macros.
Thanks all in advance



.

TOM macro to sub total a unique cell value
 

Look like a typo on my part

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.Offset(1, 0).Formula = "=SUM(G1:G498)"
End If
End Sub

worked for me.

--
Regards,
Tom Ogilvy

mary wrote in message
...
Also i am getting syntax error for statement:
rng.offset(1,0).Formula = "=SUM(G1:G498")

This is how i pasted your formula

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End If
End Sub

Could you please please tell me what i am doing wrong.
I greatly appreciate your help.
-----Original Message-----
Dim rng as Range
set rng = cells.Find("vacation1", ... other args)
if not rng is nothing then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End if

See help for the arguments to FIND or turn on the macro
recorder and do
Edit=Find manually.

Use similar code for Sick1.
--
Regards,
Tom Ogilvy


mary wrote in message
...
Help please!! is is possible to have macro that sub
total
G1:G498 and put the answer infront of a cell
that has vacation1. example if vacation1 is in A500 i
want the subtotal at B500. And the cell below vacation1
will be
sick1 and i will like to sub total H1:H498 and have the
answer in the cell in front of sick1. Example if sick1
is
in
A501 I want the subtotal at B501. I do not want to use
the cell number because it changes. only the name is
unique for each totals. And then for G498:G598 I will
like the
answer in vacation1 B600. AND so on. This project is too
long and I am not familiar with macros.
Thanks all in advance



.

TOM macro to sub total a unique cell value
 

SUPERB TOM. one more thing. I need the answer to go in
front of the cell vacation 1. So it will appear as
total of vacation1. Now it is showing on the bottom.
Thanks you made day.
-----Original Message-----
Look like a typo on my part

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.Offset(1, 0).Formula = "=SUM(G1:G498)"
End If
End Sub

worked for me.

--
Regards,
Tom Ogilvy

mary wrote in message
...
Also i am getting syntax error for statement:
rng.offset(1,0).Formula = "=SUM(G1:G498")

This is how i pasted your formula

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End If
End Sub

Could you please please tell me what i am doing wrong.
I greatly appreciate your help.
-----Original Message-----
Dim rng as Range
set rng = cells.Find("vacation1", ... other args)
if not rng is nothing then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End if

See help for the arguments to FIND or turn on the macro
recorder and do
Edit=Find manually.

Use similar code for Sick1.
--
Regards,
Tom Ogilvy


mary wrote in message
...
Help please!! is is possible to have macro that sub
total
G1:G498 and put the answer infront of a cell
that has vacation1. example if vacation1 is in A500 i
want the subtotal at B500. And the cell below
vacation1
will be
sick1 and i will like to sub total H1:H498 and have
the
answer in the cell in front of sick1. Example if
sick1
is
in
A501 I want the subtotal at B501. I do not want to
use
the cell number because it changes. only the name is
unique for each totals. And then for G498:G598 I will
like the
answer in vacation1 B600. AND so on. This project is
too
long and I am not familiar with macros.
Thanks all in advance



.



.

TOM macro to sub total a unique cell value
 

Some gave me this borders code: do not ask i do not know
how to use it(lol)
but can you add some thing like that on the code you
gave me. I will like the border on each subtotal.
With Range(cell, cell(1, 3)).Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
so the cell in front of vacation1 will contain the
total and border.
Sorry of about this but i am suck in doing the macro thing.
Thanks a million.
-----Original Message-----
Look like a typo on my part

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.Offset(1, 0).Formula = "=SUM(G1:G498)"
End If
End Sub

worked for me.

--
Regards,
Tom Ogilvy

mary wrote in message
...
Also i am getting syntax error for statement:
rng.offset(1,0).Formula = "=SUM(G1:G498")

This is how i pasted your formula

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End If
End Sub

Could you please please tell me what i am doing wrong.
I greatly appreciate your help.
-----Original Message-----
Dim rng as Range
set rng = cells.Find("vacation1", ... other args)
if not rng is nothing then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End if

See help for the arguments to FIND or turn on the macro
recorder and do
Edit=Find manually.

Use similar code for Sick1.
--
Regards,
Tom Ogilvy


mary wrote in message
...
Help please!! is is possible to have macro that sub
total
G1:G498 and put the answer infront of a cell
that has vacation1. example if vacation1 is in A500 i
want the subtotal at B500. And the cell below
vacation1
will be
sick1 and i will like to sub total H1:H498 and have
the
answer in the cell in front of sick1. Example if
sick1
is
in
A501 I want the subtotal at B501. I do not want to
use
the cell number because it changes. only the name is
unique for each totals. And then for G498:G598 I will
like the
answer in vacation1 B600. AND so on. This project is
too
long and I am not familiar with macros.
Thanks all in advance



.



.

TOM macro to sub total a unique cell value
 

In front of is kind of ambiguous. Do you mean to the right of vaction1?

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.Offset(0, 1).Formula = "=SUM(G1:G498)"
rng.Offset(0,1).borderaround weight:=xlMedium
End If

End Sub

if you want it to the left, then

rng.offset(0,-1).Formula
rng.offset(0,-1).BorderAround
--
Regards,
Tom Ogilvy



mary wrote in message
...
Some gave me this borders code: do not ask i do not know
how to use it(lol)
but can you add some thing like that on the code you
gave me. I will like the border on each subtotal.
With Range(cell, cell(1, 3)).Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
so the cell in front of vacation1 will contain the
total and border.
Sorry of about this but i am suck in doing the macro thing.
Thanks a million.
-----Original Message-----
Look like a typo on my part

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.Offset(1, 0).Formula = "=SUM(G1:G498)"
End If
End Sub

worked for me.

--
Regards,
Tom Ogilvy

mary wrote in message
...
Also i am getting syntax error for statement:
rng.offset(1,0).Formula = "=SUM(G1:G498")

This is how i pasted your formula

Sub supervisor()
Dim rng As Range
Set rng = Cells.Find("vacation1")
If Not rng Is Nothing Then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End If
End Sub

Could you please please tell me what i am doing wrong.
I greatly appreciate your help.
-----Original Message-----
Dim rng as Range
set rng = cells.Find("vacation1", ... other args)
if not rng is nothing then
rng.offset(1,0).Formula = "=SUM(G1:G498")
End if

See help for the arguments to FIND or turn on the macro
recorder and do
Edit=Find manually.

Use similar code for Sick1.
--
Regards,
Tom Ogilvy


mary wrote in message
...
Help please!! is is possible to have macro that sub
total
G1:G498 and put the answer infront of a cell
that has vacation1. example if vacation1 is in A500 i
want the subtotal at B500. And the cell below
vacation1
will be
sick1 and i will like to sub total H1:H498 and have
the
answer in the cell in front of sick1. Example if
sick1
is
in
A501 I want the subtotal at B501. I do not want to
use
the cell number because it changes. only the name is
unique for each totals. And then for G498:G598 I will
like the
answer in vacation1 B600. AND so on. This project is
too
long and I am not familiar with macros.
Thanks all in advance



.



.