Getting an exact match in a search

Jeddo

New Member
Joined
Jan 26, 2019
Messages
21
Hello, new to VBA, so forgive my ignorance.

I created a spreadsheet which I am adding data and assigning a number to the data. I have a search setup to make sure the number I am assigning is not already used. It works great except it cannot differentiate between say 75 and 175 or 1075, which is a problem I can't figure out how to get around

Here is my line of code:

Set rngCust = ActiveWorkbook.Worksheets("Auction Items").Range("B6:B240").Find(Me.txtAddItem)

If rngCust Is Nothing Then

How do I get it to do an exact match to me.txtAddItem?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this

Code:
Set rngCust = ActiveWorkbook.Worksheets("Auction Items").Range("B6:B240").Find(Me.txtAddItem, , xlvalues, xlwhole)

If the content of the textbox is a number and in the cells you also have numbers, then try:

Code:
Set rngCust = ActiveWorkbook.Worksheets("Auction Items").Range("B6:B240").Find(Val(Me.txtAddItem), , xlvalues, xlwhole)
 

Jeddo

New Member
Joined
Jan 26, 2019
Messages
21
Try this

Code:
Set rngCust = ActiveWorkbook.Worksheets("Auction Items").Range("B6:B240").Find(Me.txtAddItem, , xlvalues, xlwhole)

If the content of the textbox is a number and in the cells you also have numbers, then try:

Code:
Set rngCust = ActiveWorkbook.Worksheets("Auction Items").Range("B6:B240").Find(Val(Me.txtAddItem), , xlvalues, xlwhole)



Many thanks sir. I tried the second line as I have only numbers in the textbox and the cells I am searching. Worked like a charm. Thanks for also keeping it simple for this novice.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,112
Messages
5,576,179
Members
412,703
Latest member
sainayzawhtwe
Top