How do I restore the LookIn, LookAt, SearchOrder in FIND

When I've used a macro which uses the FIND method, the settings of
LookIn, LookAt and SearchOrder are changed for the CTRL-F function!

So how can I restore the settings for LookIn, LookAt and SearchOrder
when I exit a macro?

Thanks!

If you asking if you can query the state of those settings in your macro, to
the best of my knowledge, the answer is no.

So I don't know how you would reset them unless you have a standard
setting - then you could execute a dummy find with those settings at the end
of your macro.

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> When I've used a macro which uses the FIND method, the settings of
> LookIn, LookAt and SearchOrder are changed for the CTRL-F function!
>
> So how can I restore the settings for LookIn, LookAt and SearchOrder
> when I exit a macro?
>
> Thanks!
>

Hi,

i don't know how to get the settings of the Find directly.
how about testing to find like this:

(assuming ThisWorkbook has a sheet named "FindParam" as a work area.
"What" and "MatchCase" parameters also seem to be saved. but i
couldn't get them)

Function GetFindParam() As Long
Dim r As Range
Dim Ret As Long, i As Long
On Error GoTo ErrorHandler
With ThisWorkbook.Sheets("FindParam")
.Range("A1:B5").Clear
.Range("A2,A4,B1,B3").Value = "ab"
.Range("A3,A5,B2,B4").Value = "a"
.Range("A2:A3,B1:B2").NumberFormat = ";;;"
Set r = .Range("A1:B5").Find(What:="a")
If r.Column = 1 Then
Ret = 4
i = r.Row - 2
Else
i = r.Row - 1
End If
.Range("A1:A2").Find What:=""
End With
GetFindParam = Ret Or Array(0, 2, 1, 3)(i)
Exit Function
ErrorHandler:
GetFindParam = -1
Exit Function
End Function

Sub StoreFindParm()
ThisWorkbook.Sheets("FindParam").Range("D1").Value = GetFindParam()
End Sub

Sub RestoreFindParm()
Dim Ret As Long
With ThisWorkbook.Sheets("FindParam")
Ret = .Range("D1").Value
If Ret >= 0 Then
.Range("A1:A2").Find What:="", _
LookIn:=IIf(Ret And 1, xlValues, xlFormulas), _
LookAt:=IIf(Ret And 2, xlWhole, xlPart), _
SearchOrder:=IIf(Ret And 4, xlByColumns, xlByRows)
End If
End With
End Sub

Sub Test_GetFindParam()
Dim Ret As Long
Dim s As String

Cells.Find What:="*", _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows

StoreFindParm
RestoreFindParm

Ret = GetFindParam()
If Ret >= 0 Then
If Ret And 1 Then s = "Values" Else s = "Formulas"
If Ret And 2 Then s = s & ", Whole" Else s = s & ", Part"
If Ret And 4 Then s = s & ", ByColumns" Else s = s & ", ByRows"
End If
MsgBox s
End Sub

--
HTH,

okaizawa

[email protected] wrote:
> When I've used a macro which uses the FIND method, the settings of
> LookIn, LookAt and SearchOrder are changed for the CTRL-F function!
>
> So how can I restore the settings for LookIn, LookAt and SearchOrder
> when I exit a macro?
>
> Thanks!
>

sorry, i had forgotten about Comments.

Function GetFindParam() As Long
Dim r As Range
Dim Ret As Long, i As Long
On Error GoTo ErrorHandler
With ThisWorkbook.Sheets("FindParam")
.Range("A1:B7").Clear
.Range("A2,A4,B1,B3").Value = "ab"
.Range("A3,A5,B2,B4").Value = "a"
.Range("A2:A3,B1:B2").NumberFormat = ";;;"
.Range("A6").AddComment.Text Text:="ab"
.Range("A7").AddComment.Text Text:="a"
.Range("B5").AddComment.Text Text:="ab"
.Range("B6").AddComment.Text Text:="a"
Set r = .Range("A1:B7").Find(What:="a")
If r.Column = 1 Then
Ret = 8
i = r.Row - 2
Else
i = r.Row - 1
End If
.Range("A1:A2").Find What:=""
End With
GetFindParam = Ret Or Array(0, 4, 1, 5, 2, 6)(i)
Exit Function
ErrorHandler:
GetFindParam = -1
Exit Function
End Function

Sub StoreFindParm()
ThisWorkbook.Sheets("FindParam").Range("D1").Value = GetFindParam()
End Sub

Sub RestoreFindParm()
Dim Ret As Long
With ThisWorkbook.Sheets("FindParam")
Ret = .Range("D1").Value
If Ret >= 0 Then
.Range("A1:A2").Find What:="", _
LookIn:=IIf(Ret And 2, xlComments, _
IIf(Ret And 1, xlValues, xlFormulas)), _
LookAt:=IIf(Ret And 4, xlWhole, xlPart), _
SearchOrder:=IIf(Ret And 8, xlByColumns, xlByRows)
End If
End With
End Sub

Sub Test_GetFindParam()
Dim Ret As Long
Dim s As String

Cells.Find What:="*", _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows

StoreFindParm
RestoreFindParm

Ret = GetFindParam()
If Ret >= 0 Then
Select Case Ret And 3
Case 0: s = "Formulas"
Case 1: s = "Values"
Case 2: s = "Comments"
End Select
If Ret And 4 Then s = s & ", Whole" Else s = s & ", Part"
If Ret And 8 Then s = s & ", ByColumns" Else s = s & ", ByRows"
End If
MsgBox s
End Sub

--

okaizawa