Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop clean up
Good Morning
I am new to VBA and have the following Macro that works well but I need a little help cleaning it up Sub FillDown() Dim r As Range Dim rd As Range Dim ro As Range Set r = Range("C6") Do While Not IsEmpty(r) Set rd = r.Offset(1, 0) Set ro = r.Offset(0, 2) ro.FormulaR1C1 = "=RC[-1]/R201C4" Set r = rd Loop End Sub First thing is I want it to stop at the first empty cells it finds in column C here is an example: (you are seeing column C and D only) Energy ( Enrg ) 11,317,168.00 Services Non-Cyclical ( SNcy ) 10,125,000.00 Energy ( Enrg ) 9,950,000.00 Telecommunications ( TCom ) 9,180,000.00 Utility ( Util ) 8,079,375.00 Services Cyclical ( SCyc ) 7,175,000.00 Consumer Non-Cyclical ( CNcy ) 7,087,500.00 Utility ( Util ) 6,675,000.00 Basic Industry ( Basc ) 6,445,565.00 Insurance ( Insr ) 6,265,000.00 82,299,608.00 Telecommunications ( TCom ) 6,183,000.00 Utility ( Util ) 6,150,000.00 Technology & Electronics ( Tech ) 6,119,250.00 Consumer Cyclical ( CCyc ) 6,076,289.00 Basic Industry ( Basc ) 5,924,462.00 Insurance ( Insr ) 5,849,328.00 Media ( Medi ) 5,804,500.00 So it should stop at the cell between insurance and telecommunications.. Also if the data is modified the total that the formula pulls from will move up or down, how would I set the formula to find the Grand Total if data is added or reduced? Hope I explained this well enough, look forwward to the help! Thanks in advanced |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop clean up
Please ignore first part of question it appears to stop at the first blank
cell all I need is some assistance on the second part of my question, I apologize for the confusion. Thanks again "wblake0926" wrote: Good Morning I am new to VBA and have the following Macro that works well but I need a little help cleaning it up Sub FillDown() Dim r As Range Dim rd As Range Dim ro As Range Set r = Range("C6") Do While Not IsEmpty(r) Set rd = r.Offset(1, 0) Set ro = r.Offset(0, 2) ro.FormulaR1C1 = "=RC[-1]/R201C4" Set r = rd Loop End Sub First thing is I want it to stop at the first empty cells it finds in column C here is an example: (you are seeing column C and D only) Energy ( Enrg ) 11,317,168.00 Services Non-Cyclical ( SNcy ) 10,125,000.00 Energy ( Enrg ) 9,950,000.00 Telecommunications ( TCom ) 9,180,000.00 Utility ( Util ) 8,079,375.00 Services Cyclical ( SCyc ) 7,175,000.00 Consumer Non-Cyclical ( CNcy ) 7,087,500.00 Utility ( Util ) 6,675,000.00 Basic Industry ( Basc ) 6,445,565.00 Insurance ( Insr ) 6,265,000.00 82,299,608.00 Telecommunications ( TCom ) 6,183,000.00 Utility ( Util ) 6,150,000.00 Technology & Electronics ( Tech ) 6,119,250.00 Consumer Cyclical ( CCyc ) 6,076,289.00 Basic Industry ( Basc ) 5,924,462.00 Insurance ( Insr ) 5,849,328.00 Media ( Medi ) 5,804,500.00 So it should stop at the cell between insurance and telecommunications.. Also if the data is modified the total that the formula pulls from will move up or down, how would I set the formula to find the Grand Total if data is added or reduced? Hope I explained this well enough, look forwward to the help! Thanks in advanced |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop clean up
Option Explicit
Sub filldown() With Range(Range("E6"), Range("C6").End(xlDown).Offset(, 2)) .FormulaR1C1 = "=RC[-1]/R201C4" End With End Sub "wblake0926" wrote: Good Morning I am new to VBA and have the following Macro that works well but I need a little help cleaning it up Sub FillDown() Dim r As Range Dim rd As Range Dim ro As Range Set r = Range("C6") Do While Not IsEmpty(r) Set rd = r.Offset(1, 0) Set ro = r.Offset(0, 2) ro.FormulaR1C1 = "=RC[-1]/R201C4" Set r = rd Loop End Sub First thing is I want it to stop at the first empty cells it finds in column C here is an example: (you are seeing column C and D only) Energy ( Enrg ) 11,317,168.00 Services Non-Cyclical ( SNcy ) 10,125,000.00 Energy ( Enrg ) 9,950,000.00 Telecommunications ( TCom ) 9,180,000.00 Utility ( Util ) 8,079,375.00 Services Cyclical ( SCyc ) 7,175,000.00 Consumer Non-Cyclical ( CNcy ) 7,087,500.00 Utility ( Util ) 6,675,000.00 Basic Industry ( Basc ) 6,445,565.00 Insurance ( Insr ) 6,265,000.00 82,299,608.00 Telecommunications ( TCom ) 6,183,000.00 Utility ( Util ) 6,150,000.00 Technology & Electronics ( Tech ) 6,119,250.00 Consumer Cyclical ( CCyc ) 6,076,289.00 Basic Industry ( Basc ) 5,924,462.00 Insurance ( Insr ) 5,849,328.00 Media ( Medi ) 5,804,500.00 So it should stop at the cell between insurance and telecommunications.. Also if the data is modified the total that the formula pulls from will move up or down, how would I set the formula to find the Grand Total if data is added or reduced? Hope I explained this well enough, look forwward to the help! Thanks in advanced |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop clean up
Thank you so much. This is a lot cleaner than what I had... appeciated TONS!!!
"Patrick Molloy" wrote: Option Explicit Sub filldown() With Range(Range("E6"), Range("C6").End(xlDown).Offset(, 2)) .FormulaR1C1 = "=RC[-1]/R201C4" End With End Sub "wblake0926" wrote: Good Morning I am new to VBA and have the following Macro that works well but I need a little help cleaning it up Sub FillDown() Dim r As Range Dim rd As Range Dim ro As Range Set r = Range("C6") Do While Not IsEmpty(r) Set rd = r.Offset(1, 0) Set ro = r.Offset(0, 2) ro.FormulaR1C1 = "=RC[-1]/R201C4" Set r = rd Loop End Sub First thing is I want it to stop at the first empty cells it finds in column C here is an example: (you are seeing column C and D only) Energy ( Enrg ) 11,317,168.00 Services Non-Cyclical ( SNcy ) 10,125,000.00 Energy ( Enrg ) 9,950,000.00 Telecommunications ( TCom ) 9,180,000.00 Utility ( Util ) 8,079,375.00 Services Cyclical ( SCyc ) 7,175,000.00 Consumer Non-Cyclical ( CNcy ) 7,087,500.00 Utility ( Util ) 6,675,000.00 Basic Industry ( Basc ) 6,445,565.00 Insurance ( Insr ) 6,265,000.00 82,299,608.00 Telecommunications ( TCom ) 6,183,000.00 Utility ( Util ) 6,150,000.00 Technology & Electronics ( Tech ) 6,119,250.00 Consumer Cyclical ( CCyc ) 6,076,289.00 Basic Industry ( Basc ) 5,924,462.00 Insurance ( Insr ) 5,849,328.00 Media ( Medi ) 5,804,500.00 So it should stop at the cell between insurance and telecommunications.. Also if the data is modified the total that the formula pulls from will move up or down, how would I set the formula to find the Grand Total if data is added or reduced? Hope I explained this well enough, look forwward to the help! Thanks in advanced |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help me clean up this code | Excel Programming | |||
Clean up help | Excel Programming | |||
Help me clean this up... | Excel Programming | |||
Clean up a string | Excel Programming | |||
=clean(a1) | Excel Programming |