How to loop through cells in a column?
 

My goal is to find a row of data that has more than 255 characters in column L.

This works for one cell:
x = Len(Range("L2"))

Can someone help me with looping through cells L2, L3, L4 and so on--keeping
in mind that I need to use the Len function--so that I can perform an action
when I find a row that meets the criteria.

Thank you,
Judy

How to loop through cells in a column?
 

hi
this should work. you were a little foggy about what action you wanted to
perform so i just colored the cell.
Sub findit()
Dim lc As Long
lc = Cells(Rows.Count, "L").End(xlUp).Row
For Each cell In Range("L1:L" & lc)
If Len(cell) 255 Then
cell.Interior.ColorIndex = 3
End If
Next cell

End Sub

regards
FSt1

"Judy Ward" wrote:

My goal is to find a row of data that has more than 255 characters in column L.

This works for one cell:
x = Len(Range("L2"))

Can someone help me with looping through cells L2, L3, L4 and so on--keeping
in mind that I need to use the Len function--so that I can perform an action
when I find a row that meets the criteria.

Thank you,
Judy

How to loop through cells in a column?
 

Judy

You can loop through a collection using a For...Next loop like so

Sub Find Over255()
Dim myCell as Range
For Each myCell in Range("L1:L100")
If Len(myCell.Value)255 Then
'Do what you want here
End If
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"Judy Ward" wrote in message
...
My goal is to find a row of data that has more than 255 characters in
column L.

This works for one cell:
x = Len(Range("L2"))

Can someone help me with looping through cells L2, L3, L4 and so
on--keeping
in mind that I need to use the Len function--so that I can perform an
action
when I find a row that meets the criteria.

Thank you,
Judy

How to loop through cells in a column?
 

Dim myRng as range
dim myCell as range

with worksheets("somesheetnamehere")
set myrng = .range("L2", .cells(.rows.count,"L").end(xlup))
end with

for each mycell in myrng.cells
if len(mycell.value) 255 then
msgbox mycell.address(0,0)
'stop looking???
exit for
end if
next mycell

Judy Ward wrote:

My goal is to find a row of data that has more than 255 characters in column L.

This works for one cell:
x = Len(Range("L2"))

Can someone help me with looping through cells L2, L3, L4 and so on--keeping
in mind that I need to use the Len function--so that I can perform an action
when I find a row that meets the criteria.

Thank you,
Judy

--

Dave Peterson