Loop clean up

  • Thread starter Thread starter wblake0926
  • Start date Start date
W

wblake0926

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
 
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
 
Option Explicit
Sub filldown()
With Range(Range("E6"), Range("C6").End(xlDown).Offset(, 2))
.FormulaR1C1 = "=RC[-1]/R201C4"
End With
End Sub
 
Thank you so much. This is a lot cleaner than what I had... appeciated TONS!!!

Patrick Molloy said:
Option Explicit
Sub filldown()
With Range(Range("E6"), Range("C6").End(xlDown).Offset(, 2))
.FormulaR1C1 = "=RC[-1]/R201C4"
End With
End Sub

wblake0926 said:
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
Click to expand...
Click to expand...
 
Back
Top