Find/Search Excel Macro

Posted by Andy G. on August 13, 2001 8:10 AM

Hello.

I'm trying to write simple excel macro that will allow a user to type in a 10 digit part number, and that will then search the entire spreadsheet for that number and activate or take the user to that cell.

I was trying to record a macro where it copied the value (10-digit part number) from a certain cell and then pasted that value into the "find" function and excuted this. However, it always uses the original value (part number) that I copied when recording the macro instead of copying the new value I enter.

Can anybody help with this?

Thanks a lot!

Andy G.

Posted by Barrie Davidson on August 13, 2001 11:19 AM

Andy, is the part number a number or is it text?

Barrie

Posted by Andy G. on August 13, 2001 11:53 AM

One column of my data contains part numbers of the form 123-4567-891. That is the data I wish to search, by having them type a number in a cell and then click a button to search that column and activate the appropriate cell. So it is a NUMBER.

Thanks a lot if you can provide any help!
Andy

Posted by Barrie Davidson on August 13, 2001 12:12 PM

Andy, this should work for you.

Sub Part_Number_Search()
'Written by Barrie Davidson
Dim SearchValue As String
On Error Resume Next
SearchValue = InputBox("Enter Part Number" & Chr(13) & _
"###-####-###")
If IsError(Cells.Find(What:=SearchValue, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate) = True Then
MsgBox ("Part Number Not Found")
End If
End Sub

Regards,
Barrie

Posted by Andy G. on August 13, 2001 12:28 PM

THANKS BARRIE!!!

It works great Barrie! Thanks a lot!

Andy