Searching for a value in a specific column

Amaarouf

New Member
Joined
Oct 10, 2002
Messages
8
I'm new to VBA and I'm trying to write a macro that searches for a value in column A on a worksheet then selects the row that the value is in.

This is what I have so far:

mtcnumber = InputBox("Please enter MTC number")
Set Rng = Cells.Find(mtcnumber)
Rng.EntireRow.Select

The problem is it searches the entire worksheet for the 'MTC number' rather than just column A.

Could somebody help me out?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi Am.
Your procedure edited...
<pre>
Sub search()
Dim rng As Range
mtcnumber = InputBox("Please enter MTC number")
Set rng = ActiveSheet.UsedRange.Columns(1).Find(mtcnumber)
If Not rng Is Nothing Then
rng.EntireRow.Select
Else
MsgBox "No match found..."
End If
End Sub

</pre>
Simply narrows the search to the used range in column 1.

Tom
 
Upvote 0
Just found out a problem with the code.

It searches column A but say the user inputs a 2 digit number (24), the code may select number such as,

3124 or 2414 any number with 24.

Is there are way to ensure that it will only select a number that is searched for?
 
Upvote 0
Sure...

Highlight "Find" in your code for a list of some of the arguments you may use with the find method.

The code below is exactly the same except now we have added several arguments.

The first being "LookIn" and assigning the constant "xlValues".

The second begin "LookAt" and assigning the constant "xlWhole"

Now your find method will be more customized and lookin all values but will only return a whole value.

Searching for "ABC", for example would return TRUE for 1, but not for 2 and 3.

1. "abc"
2. "1abc23"
3. "wwxxABC""

Tom<pre>
Sub search()
Dim rng As Range
mtcnumber = InputBox("Please enter MTC number")
Set rng = ActiveSheet.UsedRange.Columns(1). _
Find(mtcnumber, , xlValues, xlWhole)
If Not rng Is Nothing Then
rng.EntireRow.Select
Else
MsgBox "No match found..."
End If
End Sub</pre>
This message was edited by TsTom on 2002-10-11 08:09
 
Upvote 0

Forum statistics

Threads
1,223,498
Messages
6,172,645
Members
452,467
Latest member
colelkay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top