set variable from .find

[Robert H]

I'm using the following to search for text in a cell and then assign
that cell to variable. later I select the entire row.

Dim lclRow As Range
With ActiveSheet.UsedRange.Cells
Set lclRow = .Find(What:="LCL")
End With

I want to do three things but am screwing up the procedure.

1. condense the selection to one line. i see no need to use the With
statement in this case.
2.Just search the first column

Ive tried a few version of the following to no avail:

Set lclRow = ActiveSheet.Columns(1).Cells.Find
(What:="LCL")

Set lclRow = Range("A:A").Find(What:="LCL")
both return nothing

3.would like to set lclRow to the entire column in the same statement
if possible.

thanks

 

[Jim Thomlinson]

You are very close. one thing to note is with Find you want to specify the
necessary arguments. If not the last vales as set by the user will be used.
MatchCase, LookIn and LookAt could cause you a problem... To select the
entire row will require a few statements as you need to deal with the case
where the search text is not found...

Dim lclRow As Range

Set lclRow = ActiveSheet.Columns(1).Find(what:="LCL", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If Not lclRow Is Nothing Then
Set lclRow = lclRow.EntireRow
End If

 

[Corey]

Robert,
try adapting something like the below:

Dim rngFound As Range
On Error Resume Next
With Worksheets("Sheet1").Range("A:A")
Set rngFound = .Find(What:="LCL"), After:=.Cells(1), LookIn:=xlValues,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, Matchbyte:=False)
If rngFound.Value <> "" then
msgbox "Found!"
end if
end with


Corey....

 

[Robert H]

Thanks for responding Jim, but I end up with the same problem. lclRow
= nothing
BTW cell A46 contains the text "LCL" without the quotes.

thanks again
Robert