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

wrote in message
oups.com...
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

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

I used the below in my Workbook_Open code to set the default values o
my Find. This way it doesn't matter what the end user had set up i
their Find, the below will over-ride it. This way they can Ctrl + f
type in the search text and click the Find button without having t
worry about case sensitivity, just in case they don't know about th
Option button and it's functions (the reason I investigated thi
because most of my end users are 1 egg shy of a dozen ;) ).


Code
-------------------
Dim dummy as Range

Set dummy = Worksheets(1).Cells.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False
-------------------


Some notes:
- Yeah, yeah, you Excel experts will find some snazzy way to simplif
or clean it up; it works and it's easy to follow.
- The variable *dummy* does nothing. It just acts as the receiver fo
the output of the Find function. After the line executes it's of n
use (at least for my application).
- Feel free to change the 'default' values to what you want for you
specific application. Remember, the value between the quotes in th
What parameter is of no consequence, in fact, you can put nothin
between them i.e. "" instead of " "

--
Air_Cooled_Nu

-----------------------------------------------------------------------
Air_Cooled_Nut's Profile: http://www.excelforum.com/member.php...fo&userid=1573
View this thread: http://www.excelforum.com/showthread.php?threadid=39432