.Find, cells with spaces

[Neal Zimm]

Hi All,
I'm building the sub below to look for values, or to see if a range is
devoid of data.

The wrinkle is that at times, cells containing ONLY spaces are deemed to
be devoid of data. In testing, so far it seems to work, but since I'm a
beginner with special cells, I'd like to know if there's a better way to make
that part of the sub more efficient. In the app I'm building, this sub will
be used a lot.

The ranges will typically vary between 1 row and lots of rows in a ws,
columns from 1 to about 90.
Thanks much,
Neal

Sub Find_Valu(Ws As Worksheet, sLookFor As String, _
FoundRow As Long, FoundCol As Integer, _
bXlWhole As Boolean, _
FmRow As Long, FmCol As Integer, _
Optional RngToRow As Long = 0, Optional RngToCol As Integer = 0, _
Optional bTestForHidden As Boolean = False, _
Optional bIgnoreSpaces As Boolean = False)
' Return the row and col of the 1st cell where a value is found.
' Zeros returned if not found. bXlwhole = true = entire cell.
' If MORE THAN ONE CELL is to be searched, RngToRow AND RngToCol parms must
be not zero.
' IF ANY cell being searched MIGHT be hidden, bTestForHidden must be True.

Dim SearchRng As Range
Dim Arg As Range, OneCell As Range
Dim WhoOrPrt
Dim Row As Long, Col As Integer
Dim HideId As String
Dim bAnyHidden As Boolean

If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart

If RngToRow = 0 Or RngToCol = 0 Then
RngToRow = FmRow
RngToCol = FmCol
End If

FoundRow = 0
FoundCol = 0

If bTestForHidden = True Then
Call Find_Hidden(Ws, FmRow, FmCol, RngToRow, RngToCol, HideId, 0)
If HideId <> "" Then bAnyHidden = True
End If

Set SearchRng = Ws.Range(Ws.Cells(FmRow, FmCol), Ws.Cells(RngToRow, RngToCol))

If bAnyHidden = False Then

Set Arg = SearchRng.Find(sLookFor, After:=Ws.Cells(RngToRow, RngToCol), _
LookIn:=xlValues, Lookat:=WhoOrPrt)
If Arg Is Nothing Then Exit Sub

'note; RmAnyValue is a public constant = "*"
If sLookFor = RmAnyValue And bIgnoreSpaces = True Then

Set Arg = SearchRng.SpecialCells(xlCellTypeConstants, xlTextValues +
xlNumbers)

For Each OneCell In Arg.Cells
If Trim(OneCell.Value) <> "" Then
''OneCell.Select 'test
''MsgBox "Value:" & OneCell.Value & Dash, , OneCell.Address 'test
FoundRow = OneCell.Row
FoundCol = OneCell.Column
Exit Sub
End If
Next OneCell

Exit Sub
End If

''Arg.Select 'test
''MsgBox "Value:" & Arg.Value & Dash, , Arg.Address 'test
FoundRow = Arg.Row
FoundCol = Arg.Column
Exit Sub

Else 'f .Find fails with hidden stuff.
For Row = FmRow To RngToRow
For Col = FmCol To RngToCol

If sLookFor <> RmAnyValue Then 'f any value is "*"
If WhoOrPrt = xlWhole Then
If Ws.Cells(Row, Col).Value = sLookFor Then
FoundRow = Row
FoundCol = Col
Exit Sub
End If
Else
If InStr(Ws.Cells(Row, Col).Value, sLookFor) > 0 Then
FoundRow = Row
FoundCol = Col
Exit Sub
End If
End If

Else 'f for any value, check length
If bIgnoreSpaces = False Then
If Ws.Cells(Row, Col).Value <> "" Then
FoundRow = Row
FoundCol = Col
Exit Sub
End If
Else 'f for any value, ignore only space(s)
If Len(Trim(Ws.Cells(Row, Col).Value)) > 0 Then
FoundRow = Row
FoundCol = Col
Exit Sub
End If
End If
End If

Next Col
Next Row
End If
End Sub

 

[JRForm]

Try testing for length:
if Len(activecell)>0 Then
do stuff
Else
End if

 

[Neal Zimm]

Thanks jr, it's what i came up with too.
i think you have to trim the cell 1st, tho' to elim the blanks, then check
len.