Wildcard filter and user prompt to search for material number in excel

[chewjekhui]

Hi there,

I need some help in terms of the code I require for prompting the user to key in the material number and using that material number as a wildcard filter to search the excel sheet and copy the values from 2 columns and copy the material numbers that contain the search term and paste it to a new excel sheet.

For example: HBA12906250000 and HBA12906280AZ0 . The alphanumeric number after HBA129062 changes and might repeat. So I want a user prompt that allows the user to key in HBA129062 and all possible combinations of that to be copied and pasted in a new sheet.

It then also copies and paste the plant stock value and plant stock value alongside with the material numbers copied earlier.

Material Number	Type		Std Cost	Material	Plant stock	Plant stock value
HBA12906250000	YRAW	BAR	11.82	0	1.51	17.85
HBA12906250000	YRAW	BAR	22.14	1	33	730.62
HBA12906280AZ0	YRAW	BAR	39.64	1	9.375	371.63
HBA12906280AZ0	YRAW	BAR	10.64	1	144.5	1537.48
HBA12906250000	YRAW	BAR	1.88	1	47.125	88.6
HBA137030500000	YRAW	BAR	3.7	1	97.072	359.17
HBA137030800000	YRAW	BAR	5.1	1	18.5	94.35
HBA137030500000	YRAW	BAR	20.87	1	126.01	2629.83
HBA137030800000	YRAW	BAR	27.98	1	8.375	234.33
HBA137030900000	YRAW	BAR	22.84	1	5.376	122.79
HBA137030900000	YRAW	BAR	17.68	1	13.02	230.19

<colgroup><col><col><col><col span="3"><col></colgroup><tbody>
</tbody>

I have been searching around forums and so far I am only able to find code that has the search criteria fixed in the VBA code. Could not get wildcard filter VBA codes.

Any help is greatly appreciated

 

[aqeelnokia99]

use HBA129062*
also u can use winautomation to automate all kind of excel to excel and excel to any oracel form and to web

 

[chewjekhui]

I want the user to type n the results come out in a certain format.... hence not using the filter function in excel

 

[vcoolio]

Hello Chewjekhui,

Try the following code placed in a standard module and assigned to a button:-

Sub Test()

        Dim MatNum As String

Application.ScreenUpdating = False

        MatNum = InputBox("Please enter the first nine characters of the required material number.")
        If MatNum = vbNullString Then Exit Sub
        
Sheet2.UsedRange.Offset(1).Clear

With Sheet1.[A1].CurrentRegion
        .AutoFilter 1, "*" & MatNum & "*"
        .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
End With

Sheet2.Columns.AutoFit
Application.ScreenUpdating = True

End Sub

Following is the link to a sample file using the data you supplied in your opening post:-

http://ge.tt/4LQGHgu2

When you click on the "GO" button, an InputBox will appear asking you to enter the first nine characters from a material number. Click OK in the InPutBox and the code will filter for any material numbers starting with the nine characters and then transfer the relevant rows of data to Sheet2.

I hope that this helps.

Please test the code in a copy of your workbook first.

Cheerio,
vcoolio.