Geek Answers Handbook

Prevent Range.Find () from changing the match whole cell content user interface

After you run the first line of the following code, Excel changes the Match All Cell Content option in the user interface. As a result, the next time the user presses Ctrl + F , the search is performed in the entire contents of the cell. I don’t like the VBA macro, which changes the setting that affects the user interface, so I always run the second line, which does a different search, and sets the Match all cell contents to default value .

'execute the find and changes the entire cell content setting
Set C = MyRange.Find(SomeText, LookAt:=xlWhole)

'put the setting back to the default value (not the previous value)
MyRange.SomeText ParName, LookAt:=xlPart

The problem is that it returns the default value, not the value that the user last set.

I would prefer something like:

'store the current entire cell content setting
OldLookAt = Application.FindLookAt

'execute the find
Set C = MyRange.Find(SomeText, LookAt:=xlWhole)

'restore the original setting
Application.FindLookAt = OldLookAt

I made up Application.FindLookAtbecause I could not find it.

Is there a way Range.Findto something without affecting the user?

+4
source share
2 answers

I spent a lot of time looking at the Excel object library and MSDN documentation, and it seems impossible to recover.

Unfortunately, it also looks like Excel actually overrides and sets your custom parameter, unless you include the parameter in the default value.

Private Sub testXLLookAT()

    'Setup fake falues
    Dim mCurLookup As XlLookAt
    Range("A1").value = "Test1"
    Range("A2").value = "Test"

    'get current setting, but this doesn't work as it defaults...
    If Range("A1:A2").Find("Test").Address = "A1" Then
        mCurLookup = xlPart
    Else
        mCurLookup = xlWhole
    End If

    'your find here

    'note - mCurLookup always becomes xlWhole...
    Range("A1:A2").Find What:="Test", LookAt:=mCurLookup



End Sub

Otherwise, the above code will work - but it is not overwritten by default, even if it is not included.

+2
source

, LookAt, reset.

Function lGetCurLookAt() As Long
'--returns current default value for Range.Find method's
'    LookAt parameter.

 '--use helper cell after all data in col A
 With Cells(Rows.Count, "A").End(xlUp)(3)
   .Value = "TestPart"
   lGetCurLookAt = IIf( _
    .Find(What:="Part", SearchFormat:=False) Is Nothing, _
    xlWhole, xlPart)
   .Value = vbNullString
 End With

End Function

, enderland. , , , , A1 $A $1.

+3

More articles:


All Articles