ExcelBanter - Iterating Thru Cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Iterating Thru Cells (https://www.excelbanter.com/excel-programming/316024-iterating-thru-cells.html)

Kelvin Clayson

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

JulieD

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

Niek Otten

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

N10

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

N10

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

Kelvin Clayson

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

Kelvin Clayson

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

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