macro to sub total a unique cell value

  • Thread starter Thread starter mary
  • Start date Start date
M

mary

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