Why does this code return a nonsensical cell address

Question

So, I have this bit of code in my program

Sheets("Produtos").Select

Dim codigo As Integer
Dim xCell As String

codigo = Text_CodPro.Text

xCell = Cells(WorksheetFunction.Match(codigo, Range("B3:B3000"), 0)).Address
Planilha1.Range("A3").Select
ActiveCell.Value = xCell

It should find me the address of a cell that is, for example, in B6, instead, it says the match address is D1, an empty cell. For the life of me I can't figure out what I did wrong here.

Answer 1

Try this - a few suggestions for changes in the code

Dim rngSrch As Range
Dim codigo As Long   'prefer Long over Integer
Dim m As Variant     'so you can capture an error value if there's no match

Set rngSrch = ThisWorkbook.Sheets("Produtos").Range("B3:B3000")

codigo = 3 'CLng(Text_CodPro.Text) 'convert to Long

m = Application.Match(codigo, rngSrch, 0) 'no `Worksheetfunction` = no run-time error if no match

If Not IsError(m) Then 'got a match?
    Planilha1.Range("A3").Value = rngSrch.Cells(m).Address
Else
    MsgBox "no match for " & codigo
End If