Ok, you have now confused me on what you are wanting. Can you re-explain
what you are needing?
--
Cheers,
Ryan


"jparnold" wrote:

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
.