Find PartNumbers

[flds]

Hello friends,

I am looking to create a VBA code to do the following. Is it possible?
I have a data range with many columns (ie. Description, Vendor, Part number……).
Column ‘A’ "Description" has string of data which includes part numbers, vendor and other info. (2500+ rows)
Column ‘H’ , 'I' & 'J' are blank
Column ‘M’ has Alphanumeric part numbers.
Column ‘N’ has code numbers.
Columns ‘M’ and ‘N’ go together
I need to find the part numbers from column M by searching in Column ‘A’, (should be able to use wildcard search)
If a match is found, I need to copy the code number in column ‘N’ and place it in column ‘H’ on the same row the match is found in Column ‘A’. and also the cell address of column 'N' to be placed in column 'I'. (for verification purpose).
If no Match is found place a "NO" in Column 'J'.
I hope I have explained this correctly.

Question
How long will it take to search all the part numbers from column 'M', it need to search each cell and find a match?

Thanks
FLDS

 

[hiker95]

flds,

Thank you so much, I have been pushing you, sorry about that.
I am in the office working on the file, “Part No's > 2” in column ‘R’ is great, fantastic, I can now filter this.
I will be using both your macro’s.

Thanks for the feedback. You are very welcome. Glad I could help.

Do we have to change this line on the macro.
“lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row”

If you are using both macros, separately, or, combined, then, yes, you can use the following line of code in both:

lr = Cells(Rows.Count, 1).End(xlUp).Row

Is it possible to search part numbers without wildcard search. Search Absolute Part No (is this the right word I used)

In the below screenshot, the YELLOW cells, can be found very easily.

The GREEN cells would require a different (more complex) strategy because of the leading and/or trailing characters.

Excel 2007
	A
1	Raw Data
2	Description
3	kdfjlki kjdfk d14df1245 kjdfkjklj
4	mmfd klkdlnm ;14sw264 fdflkl ;l;fd
5	PARTS SPARE 888-test PARTS
6	jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdk
7	mmfd klkdl 14sw264 fdflkl ;l;fd
8	TUBE 444-test ZIRC-2
9	kjlkdfjkklf kljf fdsjkj 124S245 kljdkfjksd
10	kdfj 124sd957 kjlkdsfj jklfdkl
11	RETAINING RING 444-test P/N# 888-test
12	jkjk jjkldflk j kkdkfklj 236a1458d kldfjj
13	jkfhjjdks kjldfflk hhk1297c12 k;ldfkksdk
14	3 Part Numbers 444-test P/N# 888-test 333-Test
15	jkljfkjkfd dkjlkfd jkklfkkl ;4587925 ffd;lfk '
16	NUT CS SA 563 GR A 1/2"-13UNC 888-test
17	jkjk jjkldflk j kkdkfklj ;=236a1458d kldfjj
18	kdfj sjh-;124sd957 kjlkdsfj jklfdkl
19	DUCT COVER 888-test RPANDUIT G2X2LG6 444-test
20
Sheet1

 

[flds]

Hi Hiker95,

Thanks for your reply.

I will be trying the macro's tomorrow, in the office after that I will be away for 2 week, taking Christmas break. will get back to you when I return.
I am sure the macros will work well.

I hope you will have a wonderful holiday.

All the best and have fun.
FLDS

 

[hiker95]

flds,

Thanks for the feedback.

You are very welcome.

I look forward to your next reply when you get back from your Christmas break.

And, have a great Holiday Season.

 

[flds]

Hi Hiker95
I hope you had a good holiday season. Happy New Year.
The code works great. Thank you.
One more request, the code I requested, was to search for wild card part numbers. Could you please modify to find unique part numbers (without wild card) as well.
Your help would be appreciated.
Thanks
FLDS

 

[hiker95]

flds,

I hope you also had a good holiday season. And, a belated Happy New Year.

One more request, the code I requested, was to search for wild card part numbers. Could you please modify to find unique part numbers (without wild card) as well.

I would have to see another workbook with new instructions.

 

[flds]

Hi Hiker95

Giving it a second thought, while going through the data. I found majority need wild card search. So please ignore my request on post #24.
Thank you so much for all the help. It made my work so much easier and accurate.

Thanks once again.

Flds

 

[hiker95]

flds,

You are very welcome.

And, come back anytime.