VBA help

[AndrewMB]

Hello again,

I have a bit of VBA code setup, that allows a user to enter either a part name, bin number, or order number and upon clicking a button, any data relating to that part name, number or order number will be returned.

To do this, (and this may not have been the best way to do it, at all) I have used the autofilter function and have set the filter criteria by whatever variable the user types in.

The sheet works exactly how I want it to. It returns all the data there is, relating to the variable typed in.

The only problem I have, is if the user clicks the button without typing in a part name, or bin number etc... pressing it with the variable field empty, all data is returned.

Here is the code:

Dim Inp As String
Inp = Range("C5")

Sheets("SCRIPT (DO NOT MODIFY)").Select
Selection.AutoFilter Field:=2, Criteria1:=Inp

Range("A2:A54").Select
Selection.Copy
Sheets("FRONTEND").Select
Range("G25").Select
ActiveSheet.Paste

Above is just a snapshot of the bits that matter. I would like the sheet to not return any data if the variable entered does not match any criteria listed within the autofilter. As a bonus I would like it to state, "No such data" or something like that.... rather than just staying blank.

 

[VoG]

Hi. Try like this:

Dim Inp As String
Inp = Range("C5").Value
If Inp = "" Then
    MsgBox "No data", vbExclamation
    Exit Sub
End If

 

[AndrewMB]

Thank you for getting back quickly.

That works. So easy when you see it.

My next question then....

If they press the button with the variable field empty, it now states "No search criteria entered".

However, if they add a variable, and it's spelled incorrectly, or for example, if they were to put "Hello" it again returns everything, due to the variable not being listed in the autofilter listing.

I have a feeling this maybe more complicated, but I would like it if the sheet could now state "Search criteria not found" or something like that.

Any ideas?

 

[VoG]

Perhaps

Dim Inp As String
Inp = Range("C5")
If Inp = "" Then
    MsgBox "No data", vbExclamation
    Exit Sub
End If
On Error Resume Next
With Sheets("SCRIPT (DO NOT MODIFY)")
    .AutoFilter Field:=2, Criteria1:=Inp
    .Range("A2:A54").Copy Destination:=Sheets("FRONTEND").Range("G25")
End With
On Error GoTo 0

 

[AndrewMB]

Right ok, good...

My next issue...

The user has the option to search for a bin number. Typically they look like this, RC37, FC14, FC41 etc. Currently, upon entering any of those variables, the sheet returns a list of any part number that goes into making a full bin.

So it works as I want it to.

What I would like to do though, is allow the user to enter just the first two letters, FC or RC etc. They may need to do this, if they don't have the specific bin number, but know what it starts with.

I would then want the returned list to include all bins starting with the two letters stated in the variable.

I added a new column on the sheet and instead of listing the full bin number, have entered only the first two letters. In the VBA code I changed the autofilter search criteria column number accordingly.

The sheet is then filtered, but this time by the two letters in the variable. To do this I tried:

If Len(Inp) = 2 Then


However, as before, when entering no variable into the field and running the macro, all data is returned.

 

[AndrewMB]

It's ok, I've got it. Len was the right thing to use. It was my stupidity that messed it up.