Microsoft Excel - Search with "Contains" word by macro

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

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

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

Hello John

xmarker.zip

This file might do what you want.

Regards

Harry

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

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

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

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?

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

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

Thanks thanks a lot for your kind help. Others also may be helpfull may be helpful for this.

purchase price.zip

Sir macro for copy from sheet1 to new sheet2 not running as I have edited. Can you tell me the reason. If I am using excel 2013 then any changes in macro. I have attache the file.

Hello John,

It is not clear which macro you are referring to.  Since you refer to Sheet1 I am guessing that you mean the one named CopyResult but all that ends up doing is to clear the results of the Search function so I am very confused.  If this is the one you are referring to, perhaps you need to do some more work on it.

I have had a look at the macro NewSheet and modified that a bit so that it doesn't expand your workbook to the maximum row count and that reduces the size of the file by orders of magnitude and speeds up the operation.

purchase price_mod.zip

Regards

Harry

Hello again John,

I also made a change to the Worksheet_SelectionChange macro on the Data page but one line needs to be changed further as follows

With Range(Target.Offset(0, 1 - Target.Column), Target.Offset(0, 12 - Target.Column)).Interior

Regards

Harry

Yes sir, need to copy result from sheet1 to sheet2. I would like to correct the CopyResult. 

Hello John,

You should be able to get some ideas on how to go about it from the NewSheet macro.  When you have had a go and, if you still can't get there, then get back to me.

Regards

Harry

Sorry sir, I could not find out the mistake 

Hello John,

You are the one who knows what it is you want to copy from Sheet1 and where you want to copy it to on Sheet2.  In the NewSheet macro there is a section of code that does the range specification for the source sheet (in that case Data but in this case needs modification for Sheet1). 

    Sheets(DATA).Select
    With Sheets(DATA)
    intRow = Range("A" & Rows.Count).End(xlUp).Row
    intCol = Range("A1").End(xlToRight).Column - 1
    Set rngList = Range(Cells(1, 1), Cells(intRow, intCol))

There is also code to copy the selected range (in that case after selection of the filtered range but in this case there would not be that intermediate step).

    rngFilter.Copy
    End With

And there is code to paste the required data into the destination sheet (in that case Selected Rows but in this case Sheet2).

    Sheets(SR).Cells(1, 1).PasteSpecial xlAll

If you use these, suitably modified, you should be somewhere along the way to getting what you want but obviously you will have to work out the layouts and variable values to suit the new application.

Regards

Harry

JVM0.zip

Sir I have attached a file here with. In that sheet1 macro (FindInstances) work. But x mark and qty not copy to data sheet. Sir can you tell me in CopyResult() macro in which colum and row I mentioned is correct or not?. sir why this copy from sheet1 to data sheet. I have tested different values, but not working.

Hello John,

Well, for starters I suspect that when you want to determine the number of rows to check you should specify the correct address for the CurrentRegion Property so CatalogStart not ListStart.

In the second instance you are trying to set an operation equal to a value as in  A - B = C.  It should be C = A - B.

REgards

Harry

sorry sir I can't understand, can you tell me clearly.

Hello John

Where the code currently says
Set rngTable = Range("ListStart").CurrentRegion
it shoulld be
Set rngTable = Range("CatalogStart").CurrentRegion

In these two statements, the text on the right hand side of the equals sign should be on the left side of the equals sign and vice versa
Range("CatalogStart").Offset(rngTable.Offset(intLoop - 1, 1) - Range("CatalogStart").Row, 9).Value = rngTable.Offset(intLoop - 1, 12).Value
      Range("CatalogStart").Offset(rngTable.Offset(intLoop - 1, 1) - Range("CatalogStart").Row, 5).Value = rngTable.Offset(intLoop - 1, 6).Value

Regards

Harry

purchase price.zip
JVM0.zip


In purchase price.xlm macro is working properly,but in JVM0.xlm macro not working. what is the difference.

 
correction what you told is it as below?

Range("CatalogStart").Offset(rngTable.Offset(intLoop - 1, 12) - Range("CatalogStart").Row, 9).Value = rngTable.Offset(intLoop - 1, 1).Value

Range("CatalogStart").Offset(rngTable.Offset(intLoop - 1, 6) - Range("CatalogStart").Row, 5).Value = rngTable.Offset(intLoop - 1, 1).Value

John,

I am sorry but I do not understand what you are trying to say or do with the macro, or even which macro you are talking about. 

Perhaps if you can explain exactly what you are trying to do and which data you want copied where.  So a sheet with the source data highlighted and a sheet with the output that you are looking for would perhaps help.

As for the formula

rngTable.Offset(intLoop - 1, 1).Value = Range("CatalogStart").Offset(rngTable.Offset(intLoop - 1, 12) - Range("CatalogStart").Row, 9).Value

would make more sense from a programming point of view if not for achieving what you want.

Regards

Harry

Sir, as per in my file JVM0 file Data is Database sheet and Sheet1 for search and edit some item from Data sheet.

In sheet1 Row – 4 and Column – B you can search some parts number from data sheet as I have a large data sheet it is easy to find a particular item.

If you type 1752 in R4C2 (ROW4, COLUMN B) and run FindInstances macro that shows the particular item below in R7C1 onwards.

Now I need to update the Data sheet with this particular Item which searched and found in sheet1.

I need to update the quantity and Copy column of the searched and found item.

Ie

R7C12 – Qty column

R7C13 – Copy Column

I would like to add Qty 10 nos and copy column x.  Same as below

 

SEARCH ITEM

1916

ROW NO

PRODUCT ID

DESCRIPTION

SUPPLIER

UNIT Nos./Mtrs

UNIT PRICE

DISCOUNT

U/P AFTER DISCOUNT IN $

EXCHANGE RATE

CIF

TOTAL PRICE IN Dhs.

QTY

COPY

7

1916

SPROCKET BOLT WITH NUT & WASHER

Nos.

54.53

70.00%

16.36

3.68

17.00%

0.00

10

x

 When I run the CopyResult macro data sheet same particular item should updated with this.

With this I need to update my data sheet Qty = 10 nos and Copy Column x with this.


the result as below

 

	PROJECT:	CO
	CUSTOMER:
	SYSTEM :
	JOB OPENING:
SL. NO.	PRODUCT ID	DESCRIPTION	SUPPLIER	QTY	UNIT Nos./Mtrs	CURRENCY	UNIT PRICE	DISCOUNT	U/P AFTER DISCOUNT IN $	EXCHANGE RATE	CIF	UNIT PRICE IN Dhs.	TOTAL PRICE IN Dhs.	Copy
	1916	SPROCKET BOLT WITH NUT & WASHER		10	Nos.	$	54.53	70.00%	16.36	3.68	17.00%	70.44	704.35	x
	1749	SPROCKET LOCK PATTI	Local		Nos.	AED	15	0.00%	15.00	1	0.00%	15.00	0.00
	1749A	SPROCKET LOCK PATTI			Nos.	$	36.57	70.00%	10.97	3.68	17.00%	47.24	0.00
	1749B	SPROCKET LOCK PATTI /BOLT KIT COMP.			Nos.	$	74.79	70.00%	22.44	3.68	17.00%	96.60	0.00

You can check the purchase price excel file, in that this macro working properly.

Thanks and regards

Hello John,

Now that I know what you are trying to do.  I think this is what you need.
For intLoop = 1 To intCount
Range("CatalogStart").Offset(intLoop - 1, 3).Value = Range("ListStart").Offset(intLoop - 1, 11).Value
Range("CatalogStart").Offset(intLoop - 1, 13).Value = Range("ListStart").Offset(intLoop - 1, 12).Value
Next intLoop

However, if your Product ID is unique and you only ever have one row after the Search function then you will not need the For-Next loop however it does no real harm leaving it there as I imagine that it is possible that you could search on only part of the Product ID.

One other thing, because not every column that you transfer in your search function is filled, the clear current region action does not clear all data and so some Qty and Copy cells will be left filled after the next Search operation.  So perhaps you should consider whether to only transfer those cells that are absolutely necessary for the selection process or whether to artifically fill cells that will be empty during the Search process.

Regards

Harry

Sir this updating only data sheet's first record only. Here in sheet1, I  searched 1752 product ID and changed the qty column 300 and Copy column x. But this updated 1916 product  ID. Macro not updated the relevant record. Sir also I want to clear the Contents in Sheet1 after updating Data sheet. can you help to solve this problem.
regards
 

SEARCH ITEM

1752

ROW NO

PRODUCT ID

DESCRIPTION

SUPPLIER

UNIT Nos./Mtrs

UNIT PRICE

DISCOUNT

U/P AFTER DISCOUNT IN $

EXCHANGE RATE

CIF

TOTAL PRICE IN Dhs.

QTY

COPY

15

1752

STAND PIN

Nos.

21.8

70.00%

6.54

3.68

17.00%

0.00

x

 

SL. NO.	PRODUCT ID	DESCRIPTION	SUPPLIER	QTY	UNIT Nos./Mtrs	CURRENCY	UNIT PRICE	DISCOUNT	U/P AFTER DISCOUNT IN $	EXCHANGE RATE	CIF	UNIT PRICE IN Dhs.	TOTAL PRICE IN Dhs.	Copy
	1916	SPROCKET BOLT WITH NUT & WASHER		300	Nos.	$	54.53	70.00%	16.36	3.68	17.00%	70.44	21,130.59	x
	1749	SPROCKET LOCK PATTI	Local		Nos.	AED	15	0.00%	15.00	1	0.00%	15.00	0.00
	1749A	SPROCKET LOCK PATTI			Nos.	$	36.57	70.00%	10.97	3.68	17.00%	47.24	0.00
	1749B	SPROCKET LOCK PATTI /BOLT KIT COMP.			Nos.	$	74.79	70.00%	22.44	3.68	17.00%	96.60	0.00
	1744B	SPEEDOMETER LOCK PATTI	Local		Nos.	AED	4	0.00%	4.00	1	0.00%	4.00	0.00
	1744A	SPEEDOMETER LOCK PATTI	G		Nos.	$	31.14	70.00%	9.34	3.68	17.00%	40.22	0.00
	1746	SPEEDOMETER NUT			Nos.	$	35.71	70.00%	10.71	3.68	17.00%	46.13	0.00
	1912	SPEEDOMETER NUT			Nos.	$	180.82	70.00%	54.25	3.68	17.00%	233.56	0.00
	1752	STAND PIN			Nos.	$	21.8	70.00%	6.54	3.68	17.00%	28.16	0.00
	1706A	STATER ASSEMBLY FIXING SCREW			Nos.	$	54.53	70.00%	16.36	3.68	17.00%	70.44	0.00
	1753A	STEEL PLATE (SET OF 3)	Local		Nos.	AED	7	0.00%	7.00	1	0.00%	7.00	0.00

Hello John

One way to make sure that the data clears is to be sure that all cells are filled in the range of selected items and if a cell to be selected is empty then fill the selection cell with something.  From the data supplied the only data that is not filled is the Supplier column so by substituting this code

    If c.Offset(0, 2) <> "" Then
      rngFound.Offset(intCount, 3) = c.Offset(0, 2).Value
    Else
      rngFound.Offset(intCount, 3) = "N/A"
    End If

for this

rngFound.Offset(intCount, 3) = c.Offset(0, 2).Value

in the FindInstances macro will take care of that.

In the CopyResult macro, this is what you need.

For intLoop = 1 To intCount
Range("CatalogStart").Offset(Range("ListStart").Offset(intLoop - 1, 0).Value - Range("CatalogStart").Row, 3).Value = Range("ListStart").Offset(intLoop - 1, 11).Value
Range("CatalogStart").Offset(Range("ListStart").Offset(intLoop - 1, 0).Value - Range("CatalogStart").Row, 13).Value = Range("ListStart").Offset(intLoop - 1, 12).Value
Next intLoop

Regards

Harry

Sir, It shows Run_time error again. Can you please check, correct and upload the excel file.

Hello John,

You have obviously made some sort of error in copting the code into the macros but here you are.

JVM0.zip

Regards

Harry

Thanks you sir. Thank you so much for help.

I made some major errors in that that is why that not worked. 
Sir one more help I need from you, if it possible I would like to add another search in Data sheet Description column.

Now we are searching with product ID, but some product ID I could not remember so I would search with its Description.

Example - SPEEDOMETER , suppose if I don't now its product ID so I want to search with text.
Now we are searching product ID in sheet1 , Row4, ColumnB, Can we add another search & copy function Sheet1, Row4, ColumnC for Description with the same macro with new name. 

Regards.

Hello John

Replace FindInstances macro with this and you will be able to put a search term in either B4 or C4 and it will search the ProductID or Descriptinon column as appropriate.  If searching on Description then be sure to Clear  cell B4.

Sub FindInstances()

Dim rngList, rngFound As Range
Dim c As Range
Dim strSearch As String
Dim intCount As Integer, intOther As Integer

intOther = 0
strSearch = Range("SearchTerm").Value
If strSearch = "" Then
    strSearch = Range("SearchTerm").Offset(0, 1).Value
    intOther = 1
End If
intCount = 0
Set rngList = Range(Range("CatalogStart").Offset(0, intOther), Range("CatalogStart").Offset(0, intOther).End(xlDown))
Set rngFound = Range("ListStart")

For Each c In rngList
    If InStr(1, c.Value, strSearch, 1) > 0 Then
    rngFound.Offset(intCount, 0) = c.Row
    rngFound.Offset(intCount, 1) = c.Offset(0, -intOther).Value
    rngFound.Offset(intCount, 2) = c.Offset(0, 1 - intOther).Value
    If c.Offset(0, 2) <> "" Then
      rngFound.Offset(intCount, 3) = c.Offset(0, 2 - intOther).Value
    Else
      rngFound.Offset(intCount, 3) = "N/A"
    End If
    rngFound.Offset(intCount, 4) = c.Offset(0, 4 - intOther).Value
    rngFound.Offset(intCount, 5) = c.Offset(0, 6 - intOther).Value
    rngFound.Offset(intCount, 6) = c.Offset(0, 7 - intOther).Value
    rngFound.Offset(intCount, 7) = c.Offset(0, 8 - intOther).Value
    rngFound.Offset(intCount, 8) = c.Offset(0, 9 - intOther).Value
    rngFound.Offset(intCount, 9) = c.Offset(0, 10 - intOther).Value
    rngFound.Offset(intCount, 10) = c.Offset(0, 12 - intOther).Value
    rngFound.Offset(intCount, 11) = c.Offset(0, 3 - intOther).Value
    rngFound.Offset(intCount, 12) = c.Offset(0, 13 - intOther).Value
    intCount = intCount + 1
    End If
Next c
End Sub

Regards

Harry

Sir, what I have to do after copying this macro. Need to create a new catalogstart and searchterm for description search.

Sir did you tell like this, then Description and supplier column result like this

 

Search Description	patti
SEARCH ITEM
ROW NO	PRODUCT ID	DESCRIPTION	SUPPLIER	UNIT Nos./Mtrs	UNIT PRICE	DISCOUNT	U/P AFTER DISCOUNT IN $	EXCHANGE RATE	CIF	TOTAL PRICE IN Dhs.	QTY	COPY
8	SPROCKET LOCK PATTI	Local	N/A	Nos.	15	0.00%	15.00	1	0.00%	0.00
9	SPROCKET LOCK PATTI		N/A	Nos.	36.57	70.00%	10.97	3.68	17.00%	0.00
10	SPROCKET LOCK PATTI /BOLT KIT COMP.		N/A	Nos.	74.79	70.00%	22.44	3.68	17.00%	0.00
11	SPEEDOMETER LOCK PATTI	Local	N/A	Nos.	4	0.00%	4.00	1	0.00%	0.00
12	SPEEDOMETER LOCK PATTI	G	N/A	Nos.	31.14	70.00%	9.34	3.68	17.00%	0.00

JVM1.zip
I made the changes like in attachment. Sir when  FindInstances macro running

Search Description	patti
SEARCH ITEM
ROW NO	PRODUCT ID	DESCRIPTION	SUPPLIER	UNIT Nos./Mtrs	UNIT PRICE	DISCOUNT	U/P AFTER DISCOUNT IN $	EXCHANGE RATE	CIF	TOTAL PRICE IN Dhs.	QTY	COPY
8	SPROCKET LOCK PATTI	Local	N/A	Nos.	15	0.00%	15.00	1	0.00%	0.00
9	SPROCKET LOCK PATTI		N/A	Nos.	36.57	70.00%	10.97	3.68	17.00%	0.00
10	SPROCKET LOCK PATTI /BOLT KIT COMP.		N/A	Nos.	74.79	70.00%	22.44	3.68	17.00%	0.00
11	SPEEDOMETER LOCK PATTI	Local	N/A	Nos.	4	0.00%	4.00	1	0.00%	0.00
12	SPEEDOMETER LOCK PATTI	G	N/A	Nos.	31.14	70.00%	9.34	3.68	17.00%	0.00

Description and Supplier column in sheet1 is like below.

Hello John,

I did not say that you needed to redefine the CatalogStart or any other named range in your spreadsheet.  If you return the named ranges to where they were then all should be fine.

Regards

Harry

Thank you sir,

Sir in my last attached sample file (https://www.nullskull.com/FileUpload/-1635978056/JVM1.zip) there is a macro NewSheet(), Sir can you help me to change as it work in that file

Hello John,

I am not available until late September. I am afraid that if you want help before then you will either have to help yourself or hope that somebody else can chip in.  If you ever want to learn to do VBA coding then I think it would be a good idea for you to work on it yourself.

Regards

Harry