Option Explicit

Sub Main()

Dim cell As Range
Dim rng As Range
Dim rFound As Range
Set rng = _
Workbooks("Book2.xls"). _
Worksheets("Sheet1"). _
Range("A1:C10")

For Each cell In rng.Cells
If Len(cell.Text) = 4 Then
'do something
If rFound Is Nothing Then
Set rFound = cell
Else
Set rFound = Union(rFound, cell)
End If
Debug.Print cell.Address
End If
Next cell

Debug.Print "Found: "; rFound.Count
Set rng = Nothing
Set rFound = Nothing

End Sub

You wrote:
between three and five characters (?) in length?
If you mean between 2 and 6, ( 3 <= x <= 5 ) use

If Len(cell.Text) = 3 And Len(cell.Text) <= 5 Then

You can use cell.Value or cell.Text. They should return the same string for
string (text) type data. I can never remember all the subtleties, but what I
use as a rule of thumb is that Text returns what you see in the cell. Value
may return an underlying, unformatted value.


--
Bob Kilmer


"Kobayashi" wrote in message
...
I have tried to search the forum but haven't been able to find anything
so please forgive me if this has been covered previously?

How can I perform an If function, in a For... loop, whereby I wish to
find all cells with a value ( and perhaps using value is wha'ts wrong?)
between three and five characters (?) in length?

Thanks once again,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/