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!
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
I used the below in my Workbook_Open code to set the default values of my Find. This way it doesn't matter what the end user had set up in 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 to worry about case sensitivity, just in case they don't know about the Option button and it's functions (the reason I investigated this because most of my end users are 1 egg shy of a dozen ).
Some notes:Please Login or Register to view this content.
- Yeah, yeah, you Excel experts will find some snazzy way to simplify or clean it up; it works and it's easy to follow.
- The variable dummy does nothing. It just acts as the receiver for the output of the Find function. After the line executes it's of no use (at least for my application).
- Feel free to change the 'default' values to what you want for your specific application. Remember, the value between the quotes in the What parameter is of no consequence, in fact, you can put nothing between them i.e. "" instead of " ".
~Toby
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks