Iterating Thru Cells

  • Thread starter Thread starter Kelvin Clayson
  • Start date Start date
K

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
 
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
 
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
 
Hi

Could be this line

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

how about

ActiveCell.Offset(1, 0).select

Best N10
 
Repsoted

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

try

ActiveCell.Offset(1, 0).select

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

Kelvin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top