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.
[TABLE="width: 484"]
<colgroup><col><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Material Number[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD]Std Cost[/TD]
[TD]Material[/TD]
[TD]Plant stock[/TD]
[TD]Plant stock value[/TD]
[/TR]
[TR]
[TD]HBA12906250000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]11.82[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.51[/TD]
[TD="align: right"]17.85[/TD]
[/TR]
[TR]
[TD]HBA12906250000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]22.14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]730.62[/TD]
[/TR]
[TR]
[TD]HBA12906280AZ0[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]39.64[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.375[/TD]
[TD="align: right"]371.63[/TD]
[/TR]
[TR]
[TD]HBA12906280AZ0[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]10.64[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]144.5[/TD]
[TD="align: right"]1537.48[/TD]
[/TR]
[TR]
[TD]HBA12906250000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]1.88[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]47.125[/TD]
[TD="align: right"]88.6[/TD]
[/TR]
[TR]
[TD]HBA137030500000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]3.7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]97.072[/TD]
[TD="align: right"]359.17[/TD]
[/TR]
[TR]
[TD]HBA137030800000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]5.1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18.5[/TD]
[TD="align: right"]94.35[/TD]
[/TR]
[TR]
[TD]HBA137030500000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]20.87[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]126.01[/TD]
[TD="align: right"]2629.83[/TD]
[/TR]
[TR]
[TD]HBA137030800000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]27.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8.375[/TD]
[TD="align: right"]234.33[/TD]
[/TR]
[TR]
[TD]HBA137030900000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]22.84[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.376[/TD]
[TD="align: right"]122.79[/TD]
[/TR]
[TR]
[TD]HBA137030900000[/TD]
[TD]YRAW[/TD]
[TD] BAR [/TD]
[TD="align: right"]17.68[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13.02[/TD]
[TD="align: right"]230.19[/TD]
[/TR]
</tbody>[/TABLE]

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.