Microsoft Excel - Search with "Contains" word by macro

Asked By John on 16-Nov-12 09:26 AM

Sir can we search NAME OF ITEMS column search with the help of macro? At time search with a user input column just in database software search. Search with name of items columns that contain some particular words. Example if we type Amla in user input dialog box then search all the NAME OF ITEMS for the word contains Amla  on time and show the result so I can select the particular item by mouse click or enter then X mark should come to the copy column of the worksheet. There should need a user input column or Dialog box for type contained word for the search. Sir if it is possible it will be very helpful to me. In access database I do such a search.


S.N.

NAME OF ITEMS

MRP

PACKING SIZE

DISCOUNT (%)

VAT RATES (%)

ORDER QTY
IN
 PCS

ORDER QTY
IN
BOXES

NET PRICE
FOR
PCS

TOTAL
PRICE

COPY

 

AAMLKI RASAYAN -100GM

65

200

20.63

5

 

 

54.17

 

 

 

AAMVATARI RAS-20GM

35

400

20.63

5

 

 

29.17

 

 

 

AAMLKI RASAYAN -100GM

65

200

20.63

5

 

 

54.17

 

 

 

AAMVATARI RAS-20GM

35

400

20.63

5

 

 

29.17

 

 

 

AAROGYA DALIA (PUSHTAHAR) -500GM

30

30

20.63

5

 

 

25.00

 

 

 

AAROGYA VARDHANI VATI 20GM

40

400

20.63

5

 

 

33.34

 

 

 

AAROGYA VARDHANI VATI 40GM

80

400

20.63

5

 

 

66.67

 

 

 

ABHAYARISHTA-450ML

60

20

20.63

5

 

 

50.00

 

 

 

ABHRAK BHASM -5GM

13

200

20.63

5

 

 

10.83

 

 

 

AJMODADI CHURN-100 GRM

30

100

20.63

5

 

 

25.00

 

 

 

AKIK PISHTI - 5GM

15

200

20.63

5

 

 

12.50

 

 

 

AMLA CANDY 500 GRM

110

24

20.63

5

 

 

91.67

 

 

 

AMLA CHATPATA -500GM

115

24

20.63

5

 

 

95.84

 

 

 

AMLA CHURNA -100GM

20

100

20.63

5

 

 

16.67

 

 

 

AMLA MURRABA -1KG

100

12

20.63

5

 

 

83.34

 

 

 

AMRIT RASAYAN 1 KGS

160

12

20.63

5

 

 

133.34

 

 

 

AMRIT RASAYAN 500 GRM

85

24

20.63

5

 

 

70.84

 

 

 

APPLE MURRABA -1KG

140

12

20.63

5

 

 

116.67

 

 

 

ARJUN KWATH-100GM

15

50

20.63

5

 

 

12.50

 

 

 

ARJUNARISHTA-450ML

65

20

20.63

5

 

 

54.17

 

 

 

AROGYA VATI - 40GM

60

400

20.63

5

 

 

50.00

 

 

 

ARSHKALP VATI-20GM

30

400

20.63

5

 

 

25.00

 

 

 

ARSHKALP VATI-40GM

60

400

20.63

5

 

 

50.00

 

 

 

ARVINDASAVA-225ML

45

40

20.63

5

 

 

37.50

 

 

 

ASHGANDHA CAPSULE

50

240

20.63

5

 

 

41.67

 

 

 

ASHMARIHAR KWATH

20

25

20.63

5

 

 

16.67

 

 

Harry Boughen replied to John on 18-Nov-12 06:14 AM
Hello John,

You will need a macro to step through your data range cell by cell and use the InStr function to check the contents for your sub-string and if it occurs then carry out the functions that you want.

Regards

Harry
John replied to Harry Boughen on 18-Nov-12 06:46 AM
Yes sir, when I type CHURNA or Amla in the dialog box it should search and show all items in the Name of item colums and when i select and enter x mark should come to the copy column
Harry Boughen replied to John on 18-Nov-12 05:59 PM
Hello John

xmarker.zip

This file might do what you want.

Regards

Harry
John replied to Harry Boughen on 19-Nov-12 12:42 AM
po1.zip
Dear sir I attached my zip file here. I paste your macro here. but its not working in my file. Can you help me
Harry Boughen replied to John on 19-Nov-12 01:23 AM
Hello John,

I will look at your file but I did not mention that I had used named ranges (SearchTerm, ListStart and CatalogStart) so you will have to name the appropriate ranges as well as copying the macro.

If you do this let me know whether it works or not.

Regards

Harry
Harry Boughen replied to John on 19-Nov-12 05:36 AM
Hello John,

There were two problems.  The lack of named ranges and the string comparison was case sensitive.  Both fixed in the attached. At some stage you will have to code to clear the x's from the Copy Column.

po1.zip

Regards

Harry
John replied to Harry Boughen on 19-Nov-12 05:47 AM
No Sir, I did not use range name. I will add now these -  SearchTerm, ListStart and CatalogStart. If RANGE_K1 and TABLE should also need to define?
John replied to Harry Boughen on 19-Nov-12 03:32 PM
Sir, Thanks alot for your help. Sir can we show MRP, DISCOUNT%,VAT%,NET PRICE  also to the Sheet1(Select Product sheet). ie column c,e,f,i also  to the sheet1
Harry Boughen replied to John on 19-Nov-12 03:53 PM
Hello John,

Add these four lines inside the For Each loop in the FindInstances macro.

    rngFound.Offset(intCount, 2) = c.Offset(0, 1).Value
    rngFound.Offset(intCount, 3) = c.Offset(0, 3).Value
    rngFound.Offset(intCount, 4) = c.Offset(0, 4).Value
    rngFound.Offset(intCount, 5) = c.Offset(0, 7).Value

Also change the appropriate line in CopyResult to match this (change highlighted in red)

    If rngTable.Offset(intLoop - 1, 6) = "x" Then
      Range("CatalogStart").Offset(rngTable.Offset(intLoop - 1, 1) - Range("CatalogStart").Row, 9).Value = rngTable.Offset(intLoop - 1, 6).Value

You will have to add labels and possibly move your macro buttons

Regards

Harry


John replied to Harry Boughen on 21-Nov-12 11:42 AM
Thanks thanks a lot for your kind help. Others also may be helpfull may be helpful for this.