Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display cells(text) in one column based on cells which are present inother column | Excel Discussion (Misc queries) | |||
Loop Column | Excel Discussion (Misc queries) | |||
Loop through column headers to search from column name and get cell range | Excel Programming | |||
Help - loop through cells in a range that are not together (several different cells as Target) | Excel Programming | |||
HOW-TO? Loop through cells in a column | Excel Programming |