Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Wrong total but add each cell gives the correct total | Excel Worksheet Functions | |||
Pivot Table Sub Total Count Unique Dates | Excel Worksheet Functions | |||
Find total number of unique model numbers | Excel Worksheet Functions | |||
Unique print macro question | Excel Discussion (Misc queries) | |||
Can I total only a % of a number (cell) base on total of all cel | Excel Discussion (Misc queries) |