Excel VBA Find Function - Not working with formula cells

0
votes

Unable to get FIND to work with a formula cell containing text and locate the result on a sheet within a range.

I have created a button that when clicked should look at a cell, take the data and then search for the result in a column on a different sheet using exact match.

D11 holds a formula that returns a vlookup text result When i press the button i do the following

Sub Button3_Click()
    Dim strSearch As String
    Dim rng As Range
    Dim rng1 As Range
    Dim ws As Worksheet
    Dim wb As Workbook

    Set wb = Application.ActiveWorkbook
    Set ws = wb.Worksheets("Auspost_Data")

    strSearch = Worksheets("Report_Tool").Range("D11")
    wb.ws.Range("D:D").Select
    Selection.Find(strSearch)

End Sub

I expect the find to take the text value and find the exact match within the column range.

2
excelvba
Range.Find has some additional parameters that you'll want to specify: LookIn and LookAt – BigBen
Hi, i did try What:= LookIn:= and LookAt:= – David Zammit
If i say swapped strSearch to "MOORE PARK" as an example. it works fine. its something with how im setting up the string then referencing it later. – David Zammit
You want What:=strSearch, LookIn:=xlValues, LookAt:=xlWhole. – BigBen
strSearch = Worksheets("Report_Tool").Range("D11") wb.ws.Range("D:D").Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlWhole).Select ---------------error: Object does not support this property or method – David Zammit

2 Answers

0
votes

Try This:

Sub Button3_Click()

    Dim strSearch As String
    Dim rng As Range
    Dim rng1 As Range
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim fnd As Range

    Set wb = Application.ActiveWorkbook
    Set ws = wb.Worksheets("Auspost_Data")

    strSearch = Worksheets("Report_Tool").Range("D11")
    Set fnd = ws.Range("D:D").Find(strSearch)

    MsgBox fnd.Address

End Sub
0
votes

Please try something like this...

Sub Button3_Click()
    Dim ws As Worksheet, wsCriteria As Worksheet
    Dim strSearch As String
    Dim rng As Range


    If SheetExists("Auspost_Data") Then
        Set ws = Worksheets("Auspost_Data")
    Else
        MsgBox "There is no Sheet called " & ws.Name & " in the ActiveWorkbook.", vbCritical, "Sheet Not Found!"
        Exit Sub
    End If

    If SheetExists("Report_Tool") Then
        strSearch = Worksheets("Report_Tool").Range("D11")
    Else
        MsgBox "There is no Sheet called Report_Tool in the ActiveWorkbook.", vbCritical, "Sheet Not Found!"
        Exit Sub
    End If

    If strSearch = "" Then
        MsgBox "Search string is empty, there is nothing to find.", vbExclamation, "Empty Search String!"
        Exit Sub
    End If

    Set rng = ws.Range("D:D").Find(what:=strSearch, LookIn:=xlValues, lookat:=xlWhole)

    If Not rng Is Nothing Then
        MsgBox "The search string " & strSearch & " is found in the cell " & rng.Address(0, 0) & ".", vbInformation, strSearch & " Found!"
    Else
        MsgBox "The search string " & strSearch & " was not found.", vbExclamation, strSearch & " Found!"
    End If

End Sub


Function SheetExists(shName As String) As Boolean
    Dim sh As Worksheet
    On Error Resume Next
    Set sh = Worksheets(shName)
    On Error GoTo 0
    If Not sh Is Nothing Then SheetExists = True
End Function

To select the range if it is found:

Replace the last IF statement with this...

If Not rng Is Nothing Then
    ws.Select
    rng.Select
Else
    MsgBox "The search string " & strSearch & " was not found.", vbExclamation, strSearch & " Found!"
End If

Notice that I have removed the MsgBox if the rng is found which I think is not needed here specially when the code selects the rng found.