Iterating Thru Cells

[Kelvin Clayson]

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]

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

 

[Niek Otten]

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

 

[N10]

Hi

Could be this line

ActiveCell.Offset(1, 0).Activate 'Move down one cell

how about

ActiveCell.Offset(1, 0).select

Best N10

 

[N10]

Repsoted

ActiveCell.Offset(1, 0).Activate 'Move down one cell

try

ActiveCell.Offset(1, 0).select

N10

 

[Kelvin Clayson]

Julie -

Yeah that was it... almost there now!!

Thanks v.much

Kelvin

 

[Kelvin Clayson]

Thanks for that, I changed the line & this compared with the solution above
sorted this bit out - many thanks!

Kelvin