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

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

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

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