CODEWITHSUNDEEP
excelvba
Jishan
Jishan

Reputation: 1684

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

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 ö

Upvotes: 1

Views: 45

Answers (2)

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 2

John Coleman
John Coleman

Reputation: 51998

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.

Upvotes: 1

Related Questions