Find string from a single column in a different sheet and return address

Question

I am trying to find a string that lies in a specific column a different sheet (in this case, name is "Company"). This is what I wrote ("CompanyName" is the search string):

Public Function GetCompanyNumber(CompanyName) As String
    ThisWorkbook.Worksheets("Company").Select
    Range("B:B").Select
    GetCompanyNumber = Selection.Find(What:=CompanyName, After:=[A1], LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Address
End Function

However, every time it fails with Select method range class failed and returns me to line Range("B:B").Select. I have written this so that my search is only limited to the column B (preferably within cell number B20). How can I fix this?

Thanks.

If it maters, the search string contains special characters like ö

score 2 · Accepted Answer · answered Nov 09 '19 at 16:36

You'd better first check if Find() results in an error and, if not, then set GetCompanyNumber to found cell address

Furthermore since you're looking for special characters, I think it'd be more approrpiate using LookIn:=xlValues instead of LookIn:=xlFormulas

Public Function GetCompanyNumber(CompanyName As String) As String
    Dim rng As Range

    Set rng = ThisWorkbook.Worksheets("Company").Range("B:B").Find(What:=CompanyName, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)

    If Not rng Is Nothing Then GetCompanyNumber = rng.Address
End Function

You are welcome. Actually it's the same answer as @JohnColeman 's one, and his is some seconds faster so credits should be given to him.. BTW to limit search range to cell B20 just change `.Range("B:B")` to `.Range("B1:B20")` — DisplayName, Nov 09 '19 at 17:04

score 1 · Answer 2 · answered Nov 09 '19 at 16:35

I don't quite know why you are getting that particular error, but it really doesn't matter since the better alternative is to avoid select completely:

Public Function GetCompanyNumber(CompanyName) As String
    Dim ws As Worksheet
    Dim r As Range

    Set ws = Worksheets("Company")
    Set r = ws.Range("B:B").Find(What:=CompanyName, After:=[A1], LookIn:=xlFormulas, _
     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
     MatchCase:=False, SearchFormat:=False)
    If Not r Is Nothing Then GetCompanyNumber = r.Address
End Function

There is almost never a good reason to use Select in VBA programming. See How to avoid using Select in Excel VBA? for tips on how to avoid it.

Find string from a single column in a different sheet and return address

2 Answers2