Iterating Thru Cells


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
 
Ad

Advertisements

J

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
 
N

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
 
N

N10

Hi

Could be this line

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

how about

ActiveCell.Offset(1, 0).select

Best N10
 
N

N10

Repsoted

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

try

ActiveCell.Offset(1, 0).select

N10
 
Ad

Advertisements

K

Kelvin Clayson

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

Top