Consulting

Results 1 to 8 of 8

Thread: advance filter

  1. #1

    advance filter

    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 (I'm not driving)

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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

  3. #3
    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

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Are you sure that you hane a named range named "where"?

    Opps, my bad.

    First try changing
    .Worksheetfunction.CountA(.....
    to
    Application.WorksheetFunction.CountA(...

  5. #5
    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

  6. #6
    VBAX Regular
    Joined
    Dec 2018
    Posts
    23
    Location
    Your userform script needs an <End With> for the Textbox3 block.

  7. #7
    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

  8. #8
    VBAX Regular
    Joined
    Dec 2018
    Posts
    23
    Location
    Ok.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •