On Dec 17, 4:12*pm, Mike H wrote:
Hi,

Try the ammended code below

Also I don't understand what the "c.Value " is. *Is it a variable that
you created? or an object?

c is a range object in this case in the range i defined as MyRange. Objects
have properties and here we test the value property of the object (c.value).
It's often more intuative if cell is used instead of c

For Each cell In MyRange

Sub CreateNewSortRange()
'Thats great! *but I need to have EndRangeAddress be the last cell with
'a non 0 value not the first 0 value cell.

Dim MyRange As Range
Dim c as range
Dim StartRange As Variant
Dim EndRangeAdress As Variant
Set MyRange = Sheets("TestRange").Range("C5:c24")
For Each c In MyRange
* * If c.Value 0 And startRangeAddress = "" Then
* * * * startRangeAddress = c.Address
* * End If
* * If c.Value = 0 Then
* * * * EndRangeAddress = c.Offset(-1).Address
* * Exit For
* * End If
Next
* * * * MsgBox "Range Start= " & startRangeAddress
* * * * MsgBox "Range End= " & EndRangeAddress
End Sub

Mike



"jparnold" wrote:
On Dec 17, 2:19 pm, Mike H wrote:
Hi,

I understand what your trying to do with endrange but not with startrange,
perhaps this will get you a bit further

Sub CreateNewSortRange()
Dim MyRange As Range
Dim StartRange As Variant
Dim EndRangeAdress As Variant
Set MyRange = Sheets("TestRange").Range("C5:c24")
For Each c In MyRange
* * If c.Value 0 And startRangeAddress = "" Then
* * * * startRangeAddress = c.Address
* * End If
* * If c.Value = 0 Then
* * * * EndRangeAddress = c.Address
* * Exit For
* * End If
Next
* * * * MsgBox "Range Start= " & startRangeAddress
* * * * MsgBox "Range End= " & EndRangeAddress
End Sub

Mike

"jparnold" wrote:
I'm new to programming and am trying to create code to read a row
cells 5 - 24 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. *I don't get any errors, but
it deos not seem to be doing anything. *All Help is appreceated!

Sub CreateNewSortRange()

* Dim StartRange As Variant
* Dim EndRange As Variant
* Dim EndRangeAdress As Variant

* * Sheets("TestRange").Activate
* * Range("C5:c24").Select
* * Row = 3

* * For Col = 5 To 24

* * *With Worksheets("TestRange").Cells(Row, Col)

* * * * * * If ActiveCell.Value 0 Then
* * * * * * EndRangeAddress = ActiveCell.AddressLocal

* * * * * * If ActiveCell.Value = 0 Then
* * * * * * * * MsgBox "Range Start= " & StartRange
* * * * * * * * MsgBox "Range End= " & EndRangeAddress

* * * * * * End If
* * * * * *End If
* * * *End With
* * Next Col
.- Hide quoted text -

- Show quoted text -

Also I don't understand what the "c.Value " is. *Is it a variable that
you created? or an object?

Thanks again.
Jamie
.- Hide quoted text -

- Show quoted text -

Perfect!!!! Thanks So Much

Jamie