How to loop through cells in a column?

[Judy Ward]

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

 

[Jim Thomlinson]

Something like this should work

'*****************************
dim rng as range
dim rngToSearch as range

with sheets("Sheet1")
set rngtosearch = .range(.cells(2, "A"), .cells(rows.count, "A").end(xlup))
end with

for each rng in rngToSearch
if len(rng.value) > 255 then msgbox rng.address
next rng
'*****************************

 

[FSt1]

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

 

[Nick Hodge]

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
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Dave Peterson]

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