How to find the row number of a number in a list?

[novicevba]

I have a database in excel. Each row contains a unique number in column A. Column A is not not in any numeric order. I have a userform which has a text box (textbox1) in which I need to be able to type a number and then press a button (CommandButton9) to find the row number which that number is in.

The code works if I put text in the column that I am searching and search for the text, but it doesnt work when I look for numbers. The code is;

Dim x As Long
x = Application.WorksheetFunction.Match(TextBox1.Text, Range("a1:a1048576"), 0)
Range("a1").Offset(x - 1, 0).Select

When I try to use this code to find a number it does not work and I get a run time 1004 error - Unable to get the match property of the worksheetfunction class.

Please help!

Thanks

 

[hiker95]

novicevba,

Welcome to the MrExcel forum.


Try:

x = Application.Match(TextBox1.Text, Range("a1:a1048576"), 0)

'Or:

x = Application.Match(TextBox1.Text, Columns(1), 0)

 

[novicevba]

Hi Hiker 95.

I tied the amendments that you suggested and they both returned an error: runtime error '13' - type mismatch.

I dont understand why my code works with text but not numbers. Do you have any ideas why this might be? I have tried typing the match function in the database to find the cell reference and this works, and as I mentioned when I use my code to find text in the column it works fine.

Any other ideas would be much appreciated.

 

[Haseeb Avarakkan]

Hello Noviceba,

I don't have any knowledge in VBA, but I think the problem occurs MATCH couldn't found the lookup_value in the lookup_array. try something like this;

Sub Test()
On Error GoTo XX
Dim X As Long
    X = Application.Match(Range("D1"), Range("A:A"), 0)
    Range("A" & X).Select
Exit Sub
XX:
MsgBox "Couldn't found " & Range("D1").Value
End Sub

replace range with your textbox.

 

[novicevba]

Hi Haseeb,

Thanks for replying to my question.

I tried your code and I still seem to have the same problem in that it works if I try to find text but if I try to find a number then it fails.

I am totally confused?

 

[Rasm]

Have you tried the FoundCell function - instead of match - IDK if it will solve the problem - Buy worth a try.