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

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




.

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

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
.

On Dec 18, 1:36*pm, Ryan H wrote:
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
.- Hide quoted text -

- Show quoted text -

The input table has 2 rows. b5 - b24 which is the name categories and
c5 - c24 (the named area: NewDataSeriesArea) which are the Totals
values. The purpose of this is to determine the location of the first
zero value in the totals row (c5 - c24) to create the range paramater
which will be input to the 2nd half of the program which will adjust
the chart to exclude the data elements that have a zero value. I have
to create a start and end range variable value for both rows.
StartTotalsRangeAddress, EndTotalsRangeAddress,
StartCategoryTotalsRangeAddress, and EndCategoryTotalsRangeAddress. I
hope that helps, Thanks
Jamie

Oh ok. So you are just wanting the first empty cell in Range("C5:C24"), why
didn't you say so?

Dim myRange As Range

Set myRange = Range("C5").End(xlToRight).Offset(0,1)

Hope this helps! If so, click "YES" below.
--
Cheers,
Ryan


"jparnold" wrote:

On Dec 18, 1:36 pm, Ryan H wrote:
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
.- Hide quoted text -

- Show quoted text -

The input table has 2 rows. b5 - b24 which is the name categories and
c5 - c24 (the named area: NewDataSeriesArea) which are the Totals
values. The purpose of this is to determine the location of the first
zero value in the totals row (c5 - c24) to create the range paramater
which will be input to the 2nd half of the program which will adjust
the chart to exclude the data elements that have a zero value. I have
to create a start and end range variable value for both rows.
StartTotalsRangeAddress, EndTotalsRangeAddress,
StartCategoryTotalsRangeAddress, and EndCategoryTotalsRangeAddress. I
hope that helps, Thanks
Jamie
.