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
.

On Dec 19, 2:59*am, Ryan H wrote:
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
.- Hide quoted text -

- Show quoted text -

I actually need both ranges ( StartTotalsRangeAddress,
EndTotalsRangeAddress,
StartCategoryTotalsRangeAddress, and EndCategoryTotalsRangeAddress). I assumed that we caould at the end just copy the value for the EndTotalsRangeAddress variable and then perform an adjustment for the row to the left of C Row. Also I need help with the Chart to reference these ranges, what I did does it look OK?

Thanks
Jamie

On Dec 19, 2:59*am, Ryan H wrote:
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
.- Hide quoted text -

- Show quoted text -

Where do I insert this line in the code?

Jamie

Do us all a favor, post all your code and your questions at the top of your
replies? It is confusing when you post at the bottom of your replys. It
makes it hard to follow.
--
Cheers,
Ryan


"jparnold" wrote:

On Dec 19, 2:59 am, Ryan H wrote:
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
.- Hide quoted text -

- Show quoted text -

Where do I insert this line in the code?

Jamie
.

On Dec 21, 7:45*am, Ryan H wrote:
Do us all a favor, post all your code and your questions at the top of your
replies? *It is confusing when you post at the bottom of your replys. *It
makes it hard to follow.
--
Cheers,
Ryan



"jparnold" wrote:
On Dec 19, 2:59 am, Ryan H wrote:
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
.- Hide quoted text -

- Show quoted text -

Where do I insert this line in the code?

Jamie
.- Hide quoted text -

- Show quoted text -

Sorry You want me to reply to the 1st part of the thread? not to your
message at the end?

Here is te program so far. I added some comments to see if that will
help. The commented out section will not work, as I need to pick off
the value and cell address from the Totals Row. if the Cell in the
Totals Row (C5 - C24) = zero then I need the category name cell for
the equivelent cell address, i.e., if the end value for the Totals Row
is C14 then I need the location of the counterpart in Row B5 - B24.
Then reference the srart and end cell addresses in the Active Chart
(see the end of the program). I hope this helps. let me know Thanks
in advance.

Jamie

Is this what you ment? scroll to the top and type in the new items?
Any time to look at thi?

Thanks
Jamie






On Dec 21, 7:45*am, Ryan H wrote:
Do us all a favor, post all your code and your questions at the top of your
replies? *It is confusing when you post at the bottom of your replys. *It
makes it hard to follow.
--
Cheers,
Ryan



"jparnold" wrote:
On Dec 19, 2:59 am, Ryan H wrote:
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
.- Hide quoted text -

- Show quoted text -

Where do I insert this line in the code?

Jamie
.- Hide quoted text -

- Show quoted text -