CODEWITHSUNDEEP
vbaexcel
ThinkMAL
ThinkMAL

Reputation: 51

Macro using Find function and returning cell position

I'm trying to realize a function with two inputs (sheetname, word) and returning the cell location of "word" in (row , column) Here is the code for returning the row value

    Function RowFinder(sheet1 As String, word1 As String) As Integer
    Dim rng As Range
    Dim rngFound As Range

    ' I know that the word is situated btw A and C
    Set rng = Sheets(sheet1).Range("A:C")
    Set rngFound = rng.Find("word1", LookAt:=xlWhole, SearchOrder:=xlByRows)

    If rngFound Is Nothing Then
        MsgBox "not found"
    Else:
        MsgBox "found"
        RowFinder = rngFound.row
    End If

    End Function

    Private Sub CommandButton1_Click()
    Call RowFinder("Feuil1", "A")
    End Sub

I didn't manage to find "A" in Feuil1 as mentioned in the code. each time I execute the code, I have the msg "not found". -I'm sure of having it -

Thanks for help!

Upvotes: 0

Views: 9265

Answers (2)

Phil D
Phil D

Reputation: 36

You're telling it to look for "word1" (a string that says "word1") not the variable word1, remove the quotation marks. i.e. rng.Find(word1, lookat:=xlwhole)

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149287

The problem is anything within quotes will be considered as a string.

Change

rng.Find("word1"

to

rng.Find(word1

Upvotes: 2

Related Questions