On Dec 18, 11:00*am, Ryan H wrote:
Just add Exit For in your c.Value = 0 If Then statement. *I also took the
liberty of changing your variable declarations to String instead of Variants.
*Hope this helps! *If so, let me know, click "YES" below.

Sub CreateNewSortRange()

Dim MyRange As Range
Dim c As Range
Dim StartTotalsRangeAddress As String
Dim EndTotalsRangeAdress As String
Dim StartCategoryRangeAddress As String
Dim EndCategoryRangeAdress As String

* * Set MyRange = Sheets("TestRange").Range("NewDataSeriesArea")
* * For Each c In MyRange
* * * * If c.Value 0 And StartTotalsRangeAddress = "" Then

* * * * * * StartTotalsRangeAddress = c.Address

* * * * ' * *StartCategoryRangeAddress = StartTotalsRangeAddress

* * * * End If
* * * * If c.Value = 0 Then

* * * * * * EndTotalsRangeAddress = c.Offset(-1).Address
* * * * * * Exit For

* * * * ' * *EndCategoryRangeAddress = EndTotalsRangeAddress

* * * * Exit For
* * * * End If
* * Next

* * MsgBox "Totals Range Start= " & StartTotalsRangeAddress
* * MsgBox "Totals Range End= " & EndTotalsRangeAddress
* * MsgBox "Category Range Start= " & StartCategoryTotalsRangeAddress
* * MsgBox "Category Range End= " & EndCategoryTotalsRangeAddress

*' * ActiveSheet.ChartObjects("Chart 1").Activate
* ' *ActiveChart.ChartArea.Select
* ' *ActiveChart.SeriesCollection(1).XValues = "=TestRange!
StartTotalsRangeAddress: EndTotalsRangeAddress ""
* ' *ActiveChart.SeriesCollection(1).Values = "=TestRange!
StartCategoryTotalsRangeAddress: EndCategoryTotalsRangeAddress ""

End Sub

--
Cheers,
Ryan



"jparnold" wrote:
I'm new to programming and am trying to create code to read a row
cells C5 - C24 and determine what cell has a 0 value (The row is
always sorted by descending values). *So I want to read each cell
starting with C5 through the end of the range C24, and stop when it
gets the first 0 value. *Here is my code so far. (with the help from a
previous reply to a post).

I also am trying to capture the start and end cell range for the row
adjecent (B5 - B24) but I only need to capture the Values from row "C"
and make them for row "B". *I've commented out the offending
statements to help you see what I have so far. *Then I am trying to
inbed these four values in a chart on the same sheet. * Thank you in
advance all Help is appreceated!

Jamie

Sub CreateNewSortRange()
Dim MyRange As Range
Dim c As Range
Dim StartTotalsRangeAddress As Variant
Dim EndTotalsRangeAdress As Variant
Dim StartCategoryRangeAddress As Variant
Dim EndCategoryRangeAdress As Variant

Set MyRange = Sheets("TestRange").Range("NewDataSeriesArea")
For Each c In MyRange
* * If c.Value 0 And StartTotalsRangeAddress = "" Then

* * * * StartTotalsRangeAddress = c.Address

* * ' * *StartCategoryRangeAddress = StartTotalsRangeAddress

* * End If
* * If c.Value = 0 Then

* * * * EndTotalsRangeAddress = c.Offset(-1).Address

* * ' * *EndCategoryRangeAddress = EndTotalsRangeAddress

* * Exit For
* * End If
Next

* * * * MsgBox "Totals Range Start= " & StartTotalsRangeAddress
* * * * MsgBox "Totals Range End= " & EndTotalsRangeAddress
* * * * MsgBox "Category Range Start= " &
StartCategoryTotalsRangeAddress
* * * * MsgBox "Category Range End= " & EndCategoryTotalsRangeAddress

*' * ActiveSheet.ChartObjects("Chart 1").Activate
* ' *ActiveChart.ChartArea.Select
* ' *ActiveChart.SeriesCollection(1).XValues = "=TestRange!
StartTotalsRangeAddress:EndTotalsRangeAddress"
* ' *ActiveChart.SeriesCollection(1).Values = "=TestRange!
StartCategoryTotalsRangeAddress:EndCategoryTotalsR angeAddress"
End Sub

.- Hide quoted text -

- Show quoted text -

I need the statments that I have commented out to be incorporated into
the program (correctly in stead of the errors that I have). I have un
commented them and tryed with your changes (below), but still have a
couple of problems. 1. the StartCategoryRangeAddress and
EndCategoryRangeAddress variables are still empty, and I get a run
time error 1004 at the end. Thanks again for your help. Jamie


Sub CreateNewSortRange()
Dim MyRange As Range
Dim c As Range
Dim StartTotalsRangeAddress As String
Dim EndTotalsRangeAdress As String
Dim StartCategoryRangeAddress As String
Dim EndCategoryRangeAdress As String


Set MyRange = Sheets("TestRange").Range("NewDataSeriesArea")
For Each c In MyRange
If c.Value 0 And StartTotalsRangeAddress = "" Then


StartTotalsRangeAddress = c.Address
StartCategoryRangeAddress = StartTotalsRangeAddress


End If
If c.Value = 0 Then


EndTotalsRangeAddress = c.Offset(-1).Address


EndCategoryRangeAddress = EndTotalsRangeAddress


Exit For
End If
Next


MsgBox "Totals Range Start= " & StartTotalsRangeAddress
MsgBox "Totals Range End= " & EndTotalsRangeAddress
MsgBox "Category Range Start= " & StartCategoryTotalsRangeAddress
MsgBox "Category Range End= " & EndCategoryTotalsRangeAddress


ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=TestRange!
StartTotalsRangeAddress:EndTotalsRangeAddress"
ActiveChart.SeriesCollection(1).Values = "=TestRange!
StartCategoryTotalsRangeAddress:EndCategoryTotalsR angeAddress"

'ActiveChart.SeriesCollection(1).XValues = "=TestRange!R5C2:R14C2"

End Sub