Prev: Force input in multiple cells in Excel form
Next: VBA--Suppress error message,corrupt file or unrecognizable format
From: jparnold on 18 Dec 2009 10:00
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




 | 
Pages: 1
Prev: Force input in multiple cells in Excel form
Next: VBA--Suppress error message,corrupt file or unrecognizable format