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