Resize Table Range to exclude zero values and Input New Range into achart object

[jparnold]

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:EndCategoryTotalsRangeAddress"
End Sub

 

[Ryan H]

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

 

[jparnold]

Just add Exit For in your c.Value = 0 If Then statement.  I also tookthe
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
















- 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:EndCategoryTotalsRangeAddress"

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

End Sub

 

[Ryan H]

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

 

[jparnold]

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

















- 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

 

[Ryan H]

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.

 

[jparnold]

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






- Show quoted text -

I actually need both ranges ( StartTotalsRangeAddress,
EndTotalsRangeAddress,
Thanks
Jamie

 

[jparnold]

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






- Show quoted text -

Where do I insert this line in the code?

Jamie

 

[Ryan H]

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.

 

[jparnold]

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







- 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

 

[jparnold]

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

Thanks
Jamie