Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro Flexability
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10
Default Macro Flexability
Hello,
I am looking to make this macro a little more flexible, if I am to add or
remove data I want this to preform the same function. can anyone help me
adjust this so this is possible Thanks for the assistance
Sub Percentages()
'
' Percentages Macro
' Macro recorded 9/30/2009 by e447207
'

Cells.find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E30").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E33").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E45").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E69").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E80").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E97").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E103").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E111").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
ActiveWindow.SmallScroll Down:=6
Cells.FindNext(After:=ActiveCell).Activate
Range("E113").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E132").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E164").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E179").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E181").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E193").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E212").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E216").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E218").Select
Selection.FormulaR1C1 = "="
Range("E218").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-212]C:R[-1]C)"
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
Default Macro Flexability
What do you have in R218C4? If you are adding or deleting rows this is going
to move and we need some way to find it dynamically. Is it the last cell in
column 4 or does it have a title or ...
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Hello,
I am looking to make this macro a little more flexible, if I am to add or
remove data I want this to preform the same function. can anyone help me
adjust this so this is possible Thanks for the assistance
Sub Percentages()
'
' Percentages Macro
' Macro recorded 9/30/2009 by e447207
'

Cells.find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E30").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E33").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E45").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E69").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E80").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E97").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E103").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E111").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
ActiveWindow.SmallScroll Down:=6
Cells.FindNext(After:=ActiveCell).Activate
Range("E113").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E132").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E164").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E179").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E181").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E193").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E212").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E216").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E218").Select
Selection.FormulaR1C1 = "="
Range("E218").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-212]C:R[-1]C)"
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10
Default Macro Flexability
Thanks for the quick response! R218C4 is the "Grand Total", it is the last
cell in column. Here is a small example of what the sheet looks like:


Short Term Investment Total 24,772,572.00
Technology & Electronics ( Tech ) 2,187,500.00
Technology & Electronics ( Tech ) 2,144,906.00
Technology & Electronics ( Tech ) 2,275,625.00
Technology & Electronics ( Tech ) 948,750.00
Technology & Electronics ( Tech ) 2,424,375.00
Technology & Electronics ( Tech ) 1,400,000.00
Technology & Electronics ( Tech ) 2,806,875.00
Technology & Electronics ( Tech ) 4,300,000.00
Technology & Electronics ( Tech ) 6,119,250.00
Technology & Electronics ( Tech ) 4,925,000.00
Technology & Electronics ( Tech ) 3,272,500.00
Technology & Electronics ( Tech ) Total 32,804,781.00
Telecommunications ( TCom ) 2,851,875.00
Telecommunications ( TCom ) 1,734,000.00
Telecommunications ( TCom ) 1,351,500.00
Telecommunications ( TCom ) 3,111,000.00
Telecommunications ( TCom ) 3,088,125.00
Telecommunications ( TCom ) 1,944,375.00
Telecommunications ( TCom ) 9,180,000.00
Telecommunications ( TCom ) 4,987,500.00
Telecommunications ( TCom ) 3,266,250.00
Telecommunications ( TCom ) 1,953,875.00
Telecommunications ( TCom ) 2,060,000.00
Telecommunications ( TCom ) 2,984,625.00
Telecommunications ( TCom ) 2,582,250.00
Telecommunications ( TCom ) 4,512,094.00
Telecommunications ( TCom ) 5,282,812.00
Telecommunications ( TCom ) 1,732,625.00
Telecommunications ( TCom ) 2,043,750.00
Telecommunications ( TCom ) 6,183,000.00
Telecommunications ( TCom ) Total 60,849,656.00
Utility ( Util ) 6,150,000.00
Utility ( Util ) 8,079,375.00
Utility ( Util ) 6,675,000.00
Utility ( Util ) Total 20,904,375.00

Grand Total 647,019,316.00

Thanks again!

"Jim Thomlinson" wrote:

What do you have in R218C4? If you are adding or deleting rows this is going
to move and we need some way to find it dynamically. Is it the last cell in
column 4 or does it have a title or ...
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Hello,
I am looking to make this macro a little more flexible, if I am to add or
remove data I want this to preform the same function. can anyone help me
adjust this so this is possible Thanks for the assistance
Sub Percentages()
'
' Percentages Macro
' Macro recorded 9/30/2009 by e447207
'

Cells.find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E30").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E33").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E45").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E69").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E80").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E97").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E103").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E111").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
ActiveWindow.SmallScroll Down:=6
Cells.FindNext(After:=ActiveCell).Activate
Range("E113").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E132").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E164").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E179").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E181").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E193").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E212").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E216").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E218").Select
Selection.FormulaR1C1 = "="
Range("E218").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-212]C:R[-1]C)"
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10
Default Macro Flexability
Just a question after I find the cells with the word total in them, I would
use offset(0,2) which would replace range("xx").select... This is still very
new to me and I am trying to record, learn and write with more flexability. I
know that the recorded macro does not work when data is added or deleted. I
am trying to see if I can cut this down a little myself but with very little
luck... Thanks for your help it appreciated!!

"wblake0926" wrote:

Hello,
I am looking to make this macro a little more flexible, if I am to add or
remove data I want this to preform the same function. can anyone help me
adjust this so this is possible Thanks for the assistance
Sub Percentages()
'
' Percentages Macro
' Macro recorded 9/30/2009 by e447207
'

Cells.find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E30").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E33").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E45").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E69").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E80").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E97").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E103").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E111").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
ActiveWindow.SmallScroll Down:=6
Cells.FindNext(After:=ActiveCell).Activate
Range("E113").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E132").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E164").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E179").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E181").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E193").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E212").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E216").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E218").Select
Selection.FormulaR1C1 = "="
Range("E218").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-212]C:R[-1]C)"
End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
Default Macro Flexability
What goes in what columns? Just a short excerpt will do.
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Thanks for the quick response! R218C4 is the "Grand Total", it is the last
cell in column. Here is a small example of what the sheet looks like:


Short Term Investment Total 24,772,572.00
Technology & Electronics ( Tech ) 2,187,500.00
Technology & Electronics ( Tech ) 2,144,906.00
Technology & Electronics ( Tech ) 2,275,625.00
Technology & Electronics ( Tech ) 948,750.00
Technology & Electronics ( Tech ) 2,424,375.00
Technology & Electronics ( Tech ) 1,400,000.00
Technology & Electronics ( Tech ) 2,806,875.00
Technology & Electronics ( Tech ) 4,300,000.00
Technology & Electronics ( Tech ) 6,119,250.00
Technology & Electronics ( Tech ) 4,925,000.00
Technology & Electronics ( Tech ) 3,272,500.00
Technology & Electronics ( Tech ) Total 32,804,781.00
Telecommunications ( TCom ) 2,851,875.00
Telecommunications ( TCom ) 1,734,000.00
Telecommunications ( TCom ) 1,351,500.00
Telecommunications ( TCom ) 3,111,000.00
Telecommunications ( TCom ) 3,088,125.00
Telecommunications ( TCom ) 1,944,375.00
Telecommunications ( TCom ) 9,180,000.00
Telecommunications ( TCom ) 4,987,500.00
Telecommunications ( TCom ) 3,266,250.00
Telecommunications ( TCom ) 1,953,875.00
Telecommunications ( TCom ) 2,060,000.00
Telecommunications ( TCom ) 2,984,625.00
Telecommunications ( TCom ) 2,582,250.00
Telecommunications ( TCom ) 4,512,094.00
Telecommunications ( TCom ) 5,282,812.00
Telecommunications ( TCom ) 1,732,625.00
Telecommunications ( TCom ) 2,043,750.00
Telecommunications ( TCom ) 6,183,000.00
Telecommunications ( TCom ) Total 60,849,656.00
Utility ( Util ) 6,150,000.00
Utility ( Util ) 8,079,375.00
Utility ( Util ) 6,675,000.00
Utility ( Util ) Total 20,904,375.00

Grand Total 647,019,316.00

Thanks again!

"Jim Thomlinson" wrote:

What do you have in R218C4? If you are adding or deleting rows this is going
to move and we need some way to find it dynamically. Is it the last cell in
column 4 or does it have a title or ...
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Hello,
I am looking to make this macro a little more flexible, if I am to add or
remove data I want this to preform the same function. can anyone help me
adjust this so this is possible Thanks for the assistance
Sub Percentages()
'
' Percentages Macro
' Macro recorded 9/30/2009 by e447207
'

Cells.find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E30").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E33").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E45").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E69").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E80").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E97").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E103").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E111").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
ActiveWindow.SmallScroll Down:=6
Cells.FindNext(After:=ActiveCell).Activate
Range("E113").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E132").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E164").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E179").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E181").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E193").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E212").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E216").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E218").Select
Selection.FormulaR1C1 = "="
Range("E218").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-212]C:R[-1]C)"
End Sub

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
Default Macro Flexability
This may need to be adjusted if my guesses as to what lands in what columns
were wrong...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

What goes in what columns? Just a short excerpt will do.
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Thanks for the quick response! R218C4 is the "Grand Total", it is the last
cell in column. Here is a small example of what the sheet looks like:


Short Term Investment Total 24,772,572.00
Technology & Electronics ( Tech ) 2,187,500.00
Technology & Electronics ( Tech ) 2,144,906.00
Technology & Electronics ( Tech ) 2,275,625.00
Technology & Electronics ( Tech ) 948,750.00
Technology & Electronics ( Tech ) 2,424,375.00
Technology & Electronics ( Tech ) 1,400,000.00
Technology & Electronics ( Tech ) 2,806,875.00
Technology & Electronics ( Tech ) 4,300,000.00
Technology & Electronics ( Tech ) 6,119,250.00
Technology & Electronics ( Tech ) 4,925,000.00
Technology & Electronics ( Tech ) 3,272,500.00
Technology & Electronics ( Tech ) Total 32,804,781.00
Telecommunications ( TCom ) 2,851,875.00
Telecommunications ( TCom ) 1,734,000.00
Telecommunications ( TCom ) 1,351,500.00
Telecommunications ( TCom ) 3,111,000.00
Telecommunications ( TCom ) 3,088,125.00
Telecommunications ( TCom ) 1,944,375.00
Telecommunications ( TCom ) 9,180,000.00
Telecommunications ( TCom ) 4,987,500.00
Telecommunications ( TCom ) 3,266,250.00
Telecommunications ( TCom ) 1,953,875.00
Telecommunications ( TCom ) 2,060,000.00
Telecommunications ( TCom ) 2,984,625.00
Telecommunications ( TCom ) 2,582,250.00
Telecommunications ( TCom ) 4,512,094.00
Telecommunications ( TCom ) 5,282,812.00
Telecommunications ( TCom ) 1,732,625.00
Telecommunications ( TCom ) 2,043,750.00
Telecommunications ( TCom ) 6,183,000.00
Telecommunications ( TCom ) Total 60,849,656.00
Utility ( Util ) 6,150,000.00
Utility ( Util ) 8,079,375.00
Utility ( Util ) 6,675,000.00
Utility ( Util ) Total 20,904,375.00

Grand Total 647,019,316.00

Thanks again!

"Jim Thomlinson" wrote:

What do you have in R218C4? If you are adding or deleting rows this is going
to move and we need some way to find it dynamically. Is it the last cell in
column 4 or does it have a title or ...
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Hello,
I am looking to make this macro a little more flexible, if I am to add or
remove data I want this to preform the same function. can anyone help me
adjust this so this is possible Thanks for the assistance
Sub Percentages()
'
' Percentages Macro
' Macro recorded 9/30/2009 by e447207
'

Cells.find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E30").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E33").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E45").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E69").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E80").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E97").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E103").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E111").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
ActiveWindow.SmallScroll Down:=6
Cells.FindNext(After:=ActiveCell).Activate
Range("E113").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E132").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E164").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E179").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E181").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E193").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E212").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E216").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E218").Select
Selection.FormulaR1C1 = "="
Range("E218").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-212]C:R[-1]C)"
End Sub

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
Default Macro Flexability
That last post would have made more sense if I had posted the code...

Public Sub AddPercents()
Dim rngGrandTotal As Range
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set rngGrandTotal = .Cells(Rows.Count, "B").End(xlUp)
Set rngToSearch = .Range(.Cells(2, "B"), rngGrandTotal.Offset(-1, 0))
End With
Set rngFound = rngToSearch.Find(what:="Total", _
LookAt:=xlPart, _
LookIn:=xlValues)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
rngFound.Offset(0, 2).Formula = "=" & rngFound.Offset(0,
1).Address _
& "/" & rngGrandTotal.Offset(0, 1).Address
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

This may need to be adjusted if my guesses as to what lands in what columns
were wrong...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

What goes in what columns? Just a short excerpt will do.
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Thanks for the quick response! R218C4 is the "Grand Total", it is the last
cell in column. Here is a small example of what the sheet looks like:


Short Term Investment Total 24,772,572.00
Technology & Electronics ( Tech ) 2,187,500.00
Technology & Electronics ( Tech ) 2,144,906.00
Technology & Electronics ( Tech ) 2,275,625.00
Technology & Electronics ( Tech ) 948,750.00
Technology & Electronics ( Tech ) 2,424,375.00
Technology & Electronics ( Tech ) 1,400,000.00
Technology & Electronics ( Tech ) 2,806,875.00
Technology & Electronics ( Tech ) 4,300,000.00
Technology & Electronics ( Tech ) 6,119,250.00
Technology & Electronics ( Tech ) 4,925,000.00
Technology & Electronics ( Tech ) 3,272,500.00
Technology & Electronics ( Tech ) Total 32,804,781.00
Telecommunications ( TCom ) 2,851,875.00
Telecommunications ( TCom ) 1,734,000.00
Telecommunications ( TCom ) 1,351,500.00
Telecommunications ( TCom ) 3,111,000.00
Telecommunications ( TCom ) 3,088,125.00
Telecommunications ( TCom ) 1,944,375.00
Telecommunications ( TCom ) 9,180,000.00
Telecommunications ( TCom ) 4,987,500.00
Telecommunications ( TCom ) 3,266,250.00
Telecommunications ( TCom ) 1,953,875.00
Telecommunications ( TCom ) 2,060,000.00
Telecommunications ( TCom ) 2,984,625.00
Telecommunications ( TCom ) 2,582,250.00
Telecommunications ( TCom ) 4,512,094.00
Telecommunications ( TCom ) 5,282,812.00
Telecommunications ( TCom ) 1,732,625.00
Telecommunications ( TCom ) 2,043,750.00
Telecommunications ( TCom ) 6,183,000.00
Telecommunications ( TCom ) Total 60,849,656.00
Utility ( Util ) 6,150,000.00
Utility ( Util ) 8,079,375.00
Utility ( Util ) 6,675,000.00
Utility ( Util ) Total 20,904,375.00

Grand Total 647,019,316.00

Thanks again!

"Jim Thomlinson" wrote:

What do you have in R218C4? If you are adding or deleting rows this is going
to move and we need some way to find it dynamically. Is it the last cell in
column 4 or does it have a title or ...
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Hello,
I am looking to make this macro a little more flexible, if I am to add or
remove data I want this to preform the same function. can anyone help me
adjust this so this is possible Thanks for the assistance
Sub Percentages()
'
' Percentages Macro
' Macro recorded 9/30/2009 by e447207
'

Cells.find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E30").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E33").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E45").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E69").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E80").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E97").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E103").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E111").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
ActiveWindow.SmallScroll Down:=6
Cells.FindNext(After:=ActiveCell).Activate
Range("E113").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E132").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E164").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E179").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E181").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E193").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E212").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E216").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E218").Select
Selection.FormulaR1C1 = "="
Range("E218").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-212]C:R[-1]C)"
End Sub

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10
Default Macro Flexability
I apologize I did not label the columns
I need to find the percentage of each category total.
the number of categories differs
It appears what you sent worked Thanks a ton! sorry for the poor explanation.
C D
E
Technology & Electronics 2,187,500.00
Technology & Electronics 2,144,906.00
Technology & Electronics 2,275,625.00
Technology & Electronics Total 25,912,231.00 (% of Grand
Total)
Telecommunications 2,851,875.00
Telecommunications 1,734,000.00
Telecommunications 1,351,500.00
Telecommunications Total 5,937,375.00 (% of Grand
Total)
and so on



"Jim Thomlinson" wrote:

What goes in what columns? Just a short excerpt will do.
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Thanks for the quick response! R218C4 is the "Grand Total", it is the last
cell in column. Here is a small example of what the sheet looks like:


Short Term Investment Total 24,772,572.00
Technology & Electronics ( Tech ) 2,187,500.00
Technology & Electronics ( Tech ) 2,144,906.00
Technology & Electronics ( Tech ) 2,275,625.00
Technology & Electronics ( Tech ) 948,750.00
Technology & Electronics ( Tech ) 2,424,375.00
Technology & Electronics ( Tech ) 1,400,000.00
Technology & Electronics ( Tech ) 2,806,875.00
Technology & Electronics ( Tech ) 4,300,000.00
Technology & Electronics ( Tech ) 6,119,250.00
Technology & Electronics ( Tech ) 4,925,000.00
Technology & Electronics ( Tech ) 3,272,500.00
Technology & Electronics ( Tech ) Total 32,804,781.00
Telecommunications ( TCom ) 2,851,875.00
Telecommunications ( TCom ) 1,734,000.00
Telecommunications ( TCom ) 1,351,500.00
Telecommunications ( TCom ) 3,111,000.00
Telecommunications ( TCom ) 3,088,125.00
Telecommunications ( TCom ) 1,944,375.00
Telecommunications ( TCom ) 9,180,000.00
Telecommunications ( TCom ) 4,987,500.00
Telecommunications ( TCom ) 3,266,250.00
Telecommunications ( TCom ) 1,953,875.00
Telecommunications ( TCom ) 2,060,000.00
Telecommunications ( TCom ) 2,984,625.00
Telecommunications ( TCom ) 2,582,250.00
Telecommunications ( TCom ) 4,512,094.00
Telecommunications ( TCom ) 5,282,812.00
Telecommunications ( TCom ) 1,732,625.00
Telecommunications ( TCom ) 2,043,750.00
Telecommunications ( TCom ) 6,183,000.00
Telecommunications ( TCom ) Total 60,849,656.00
Utility ( Util ) 6,150,000.00
Utility ( Util ) 8,079,375.00
Utility ( Util ) 6,675,000.00
Utility ( Util ) Total 20,904,375.00

Grand Total 647,019,316.00

Thanks again!

"Jim Thomlinson" wrote:

What do you have in R218C4? If you are adding or deleting rows this is going
to move and we need some way to find it dynamically. Is it the last cell in
column 4 or does it have a title or ...
--
HTH...

Jim Thomlinson


"wblake0926" wrote:

Hello,
I am looking to make this macro a little more flexible, if I am to add or
remove data I want this to preform the same function. can anyone help me
adjust this so this is possible Thanks for the assistance
Sub Percentages()
'
' Percentages Macro
' Macro recorded 9/30/2009 by e447207
'

Cells.find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Range("E7").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E30").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E33").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E45").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E69").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E80").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E97").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E103").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E111").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
ActiveWindow.SmallScroll Down:=6
Cells.FindNext(After:=ActiveCell).Activate
Range("E113").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E132").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E164").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E179").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E181").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E193").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E212").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E216").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R218C4"
Cells.FindNext(After:=ActiveCell).Activate
Range("E218").Select
Selection.FormulaR1C1 = "="
Range("E218").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-212]C:R[-1]C)"
End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
need help to update macro to office 2007 macro enabled workbook jatman Excel Discussion (Misc queries) 1 December 14th 07 01:57 PM
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 1 February 5th 07 09:31 PM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 1 June 11th 05 12:44 AM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 0 June 10th 05 03:38 PM


All times are GMT +1. The time now is 09:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement