| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determine the distance between 2 cells in a column | Excel Programming | |||
| A way to Determine visible cells on the page | Excel Programming | |||
| What Excel function would you use to determine how many cells in a | Excel Discussion (Misc queries) | |||
| Determine whether a value is in a range of cells | Excel Discussion (Misc queries) | |||
| Determine Selected Cells | Excel Programming | |||
Linear Mode
