| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating Thru Cells
Hi -
Can anyone clue me in as to why the following code won't iterate down thru cells getting the value from each & comparing it to a value obtained arlier in the function? Many thanks, Kelvin 'Loop down thru cells, finding match on Room No: Do While Not IsEmpty(ActiveCell) If ActiveCell.Interior.ColorIndex < 35 Then 'Cell not unavailable If ActiveCell.Value = RoomNumber Then If Len(StaffName) = 0 Then StaffName = ActiveCell.Value Else StaffName = StaffName & Chr$(13) & ActiveCell.Value End If End If End If ActiveCell.Offset(1, 0).Activate 'Move down one cell Loop |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Iterating Thru Cells
Hi Kelvin
looks fine to me except that a cell with only a background fill is considered empty and therefore the code will end at this point. Cheers JulieD "Kelvin Clayson" wrote in message ... Hi - Can anyone clue me in as to why the following code won't iterate down thru cells getting the value from each & comparing it to a value obtained arlier in the function? Many thanks, Kelvin 'Loop down thru cells, finding match on Room No: Do While Not IsEmpty(ActiveCell) If ActiveCell.Interior.ColorIndex < 35 Then 'Cell not unavailable If ActiveCell.Value = RoomNumber Then If Len(StaffName) = 0 Then StaffName = ActiveCell.Value Else StaffName = StaffName & Chr$(13) & ActiveCell.Value End If End If End If ActiveCell.Offset(1, 0).Activate 'Move down one cell Loop |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Iterating Thru Cells
Hi Kelvin,
Is it really a Function or did you mean a Sub? You can't activate cells from a function; you can just return a value, replacing the function call. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Kelvin Clayson" wrote in message ... Hi - Can anyone clue me in as to why the following code won't iterate down thru cells getting the value from each & comparing it to a value obtained arlier in the function? Many thanks, Kelvin 'Loop down thru cells, finding match on Room No: Do While Not IsEmpty(ActiveCell) If ActiveCell.Interior.ColorIndex < 35 Then 'Cell not unavailable If ActiveCell.Value = RoomNumber Then If Len(StaffName) = 0 Then StaffName = ActiveCell.Value Else StaffName = StaffName & Chr$(13) & ActiveCell.Value End If End If End If ActiveCell.Offset(1, 0).Activate 'Move down one cell Loop |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Iterating Thru Cells
Hi
Could be this line ActiveCell.Offset(1, 0).Activate 'Move down one cell how about ActiveCell.Offset(1, 0).select Best N10 "Kelvin Clayson" wrote in message ... Hi - Can anyone clue me in as to why the following code won't iterate down thru cells getting the value from each & comparing it to a value obtained arlier in the function? Many thanks, Kelvin 'Loop down thru cells, finding match on Room No: Do While Not IsEmpty(ActiveCell) If ActiveCell.Interior.ColorIndex < 35 Then 'Cell not unavailable If ActiveCell.Value = RoomNumber Then If Len(StaffName) = 0 Then StaffName = ActiveCell.Value Else StaffName = StaffName & Chr$(13) & ActiveCell.Value End If End If End If ActiveCell.Offset(1, 0).Activate 'Move down one cell Loop |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Iterating Thru Cells
Repsoted
ActiveCell.Offset(1, 0).Activate 'Move down one cell try ActiveCell.Offset(1, 0).select N10 "Kelvin Clayson" wrote in message ... Hi - Can anyone clue me in as to why the following code won't iterate down thru cells getting the value from each & comparing it to a value obtained arlier in the function? Many thanks, Kelvin 'Loop down thru cells, finding match on Room No: Do While Not IsEmpty(ActiveCell) If ActiveCell.Interior.ColorIndex < 35 Then 'Cell not unavailable If ActiveCell.Value = RoomNumber Then If Len(StaffName) = 0 Then StaffName = ActiveCell.Value Else StaffName = StaffName & Chr$(13) & ActiveCell.Value End If End If End If ActiveCell.Offset(1, 0).Activate 'Move down one cell Loop |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Iterating Thru Cells
Julie -
Yeah that was it... almost there now!! Thanks v.much Kelvin "JulieD" wrote in message ... Hi Kelvin looks fine to me except that a cell with only a background fill is considered empty and therefore the code will end at this point. Cheers JulieD "Kelvin Clayson" wrote in message ... Hi - Can anyone clue me in as to why the following code won't iterate down thru cells getting the value from each & comparing it to a value obtained arlier in the function? Many thanks, Kelvin 'Loop down thru cells, finding match on Room No: Do While Not IsEmpty(ActiveCell) If ActiveCell.Interior.ColorIndex < 35 Then 'Cell not unavailable If ActiveCell.Value = RoomNumber Then If Len(StaffName) = 0 Then StaffName = ActiveCell.Value Else StaffName = StaffName & Chr$(13) & ActiveCell.Value End If End If End If ActiveCell.Offset(1, 0).Activate 'Move down one cell Loop |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Iterating Thru Cells
Thanks for that, I changed the line & this compared with the solution above
sorted this bit out - many thanks! Kelvin "N10" wrote in message ... Hi Could be this line ActiveCell.Offset(1, 0).Activate 'Move down one cell how about ActiveCell.Offset(1, 0).select Best N10 "Kelvin Clayson" wrote in message ... Hi - Can anyone clue me in as to why the following code won't iterate down thru cells getting the value from each & comparing it to a value obtained arlier in the function? Many thanks, Kelvin 'Loop down thru cells, finding match on Room No: Do While Not IsEmpty(ActiveCell) If ActiveCell.Interior.ColorIndex < 35 Then 'Cell not unavailable If ActiveCell.Value = RoomNumber Then If Len(StaffName) = 0 Then StaffName = ActiveCell.Value Else StaffName = StaffName & Chr$(13) & ActiveCell.Value End If End If End If ActiveCell.Offset(1, 0).Activate 'Move down one cell Loop |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Iterating thru cells & comparing | Excel Programming | |||
| Iterating through cells problem | Excel Programming | |||
| Iterating through cells problem | Excel Programming | |||
| Iterating through Cells .Net/Excel 2003 | Excel Programming | |||
| Iterating through row cells on column Match, help please. | Excel Programming | |||
Linear Mode
