ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Trying to determine what cells within a Row have a 0 (zero) value (https://www.excelbanter.com/excel-programming/437576-trying-determine-what-cells-within-row-have-0-zero-value.html)

jparnold
Trying to determine what cells within a Row have a 0 (zero) value
 
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

Mike H
Trying to determine what cells within a Row have a 0 (zero) value
 
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
.

jparnold
Trying to determine what cells within a Row have a 0 (zero) value
 
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 -

Thats great! but I need to have EndRangeAddress be the last cell with
a non 0 value not the first 0 value cell.

jparnold
Trying to determine what cells within a Row have a 0 (zero) value
 
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

Mike H
Trying to determine what cells within a Row have a 0 (zero) va
 
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
.

jparnold
Trying to determine what cells within a Row have a 0 (zero) va
 
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


All times are GMT +1. The time now is 10:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com