advance filter

[selfteaching]

Hi
Happy New Year

Can I add something to this script so I can have a message box come up when the criteria is NOT there.
What happens now is I get all the data in the data range.

Range("AA1:AC1731").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("where"), CopyToRange:=Range("result"), Unique:=False

Mike :beerchug: (I'm not driving)

[mikerickson]

Try


With Range("where")
If .WorksheetFunction.Counta(.Cells) <= (.Cells.Count / 2) Then
MsgBox "no criteria have been entered in the criteria range"
Else
Range("AA1:AC1731").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
.Cells, CopyToRange:=Range("result"), Unique:=False
End If
End With

[selfteaching]

Happy New Year
I keep getting an error for this line>>>>
If .WorksheetFunction.CountA(.Cells) <= (.Cells.Count / 2) Then
Run time error 438...Object doesn't support this property or method

Sub find()
Application.ScreenUpdating = False
Application.Goto Reference:="R1C1"
With Range("where")
If .WorksheetFunction.CountA(.Cells) <= (.Cells.Count / 2) Then
MsgBox "no criteria have been entered in the criteria range"
Else
Range("AA1:AC1731").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
.Cells, CopyToRange:=Range("result"), Unique:=False
End If
End With
Application.Goto Reference:="erasewhat"
' this erases the criteria range for the next time the macro has to run
Selection.ClearContents
Application.Goto Reference:="R1C1"
Application.ScreenUpdating = True
End Sub
I also tried:
I made the line where the results start and called it “answer”
I then changed>> With Range(“where”) to With Range(“answer”)

Before I asked this question, I played around with Errorhandling and msgbox.

Sub find()
Application.ScreenUpdating = False
'On Error GoTo errorhandling
Application.Goto Reference:="R1C1"
Range("AA1:AC1731").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("where"), CopyToRange:=Range("result"), Unique:=False
Application.Goto Reference:="erasewhat" ‘this erases the criteria box after the filter works
Selection.ClearContents
Application.Goto Reference:="R1C1"
'errorhandling: MsgBox (" Not Valid Try Again")
Userform1.show
Application.ScreenUpdating = True
End Sub
I’m trying to have a message box come up when wrong input is put in a text box in a userform
After the data is put in and the commandbutton is clicked, the macro “find” starts
If the input doesn’t match the data range, nothing shows. I want the message box to show that wrong data has been entered and them the userform comes up again,But this time with the list box empty of any data.

Here is the script for the userform

Private Sub CommandButton1_Click()
With TextBox1 'city
ActiveSheet.Range("af2").Value = .Text
End With
With TextBox3 'zip code
ActiveSheet.Range("ag2").Value = .Text
With TextBox10 'area code
ActiveSheet.Range("ah2").Value = .Text
End With
Me.Hide
End With
find
Unload Me
End Sub
Also, is there a way to stop debugging on the vba screen with out closing it out. After trying a change, a box comes up and tells me i'm in break mode/
Thank you for taking the time to help
Please don't interrupt your holiday plans
next year is ok this
mike :beerchug: :beerchug:

[mikerickson]

Are you sure that you hane a named range named "where"?

Opps, my bad.

First try changing

.Worksheetfunction.CountA(.....

to

Application.WorksheetFunction.CountA(...

[selfteaching]

Yep. "where" is a range name.

Adding Application is what was wrong with the line
Thank you

Second question: is there a way to get out of debugging mode without closing the VBA box?


mike

[MagPower]

Your userform script needs an <End With> for the Textbox3 block.

[selfteaching]

Hi MagPower
Its there in the original script.
I shortened the macro for convenience and space when I posted it here and must have deleted to much.

Thanks for your concern

mike

[MagPower]

Ok.