Tags: box, copy, create, criteria, excel, filter, function, macro, microsoft, msdn, parameters, paste, running, software, value

paste into filter criteria box running macro

On Microsoft » Microsoft Excel

1,535 words with 1 Comments; publish: Fri, 23 May 2008 07:50:00 GMT; (306250.00, « »)

I need to create a macro which can filter a list from 2 parameters. It is not possible to copy a value in the criteria box of the filter function while running a macro. It can be done by hand with ctrl-c and ctrl-v but it will not work in the macro

Any clues

All Comments

Leave a comment...

  • 1 Comments
    • You can get to it like:

      Option Explicit

      Sub testme01()

      Dim myFilter As Filter

      Dim myOp As String

      Dim myCrit1 As String

      Dim myCrit2 As String

      With ActiveSheet

      If .FilterMode Then

      Set myFilter = .AutoFilter.Filters(1)

      myOp = ""

      myCrit1 = ""

      myCrit2 = ""

      With myFilter

      On Error Resume Next

      myCrit1 = .Criteria1

      myCrit2 = .Criteria2

      Select Case .Operator

      Case Is = xlAnd: myOp = " And "

      Case Is = xlOr: myOp = " Or "

      Case Is = xlBottom10Percent: myOp = " bot10pct "

      Case Is = xlTop10Items: myOp = " top10items "

      Case Is = xlBottom10Items: myOp = " bot10Items "

      Case Is = xlTop10Percent: myOp = " top10pct "

      End Select

      On Error GoTo 0

      MsgBox myCrit1 & myOp & myCrit2

      End With

      End If

      End With

      End Sub

      I looked at the first column in the filtered range:

      Set myFilter = .AutoFilter.Filters(1)

      stephan brisson wrote:

      > I need to create a macro which can filter a list from 2 parameters. It is not possible to copy a value in the criteria box of the filter function while running a macro. It can be done by hand with ctrl-c and ctrl-v but it will not work in the macro.

      > Any clues?

      --

      Dave Peterson

      ec35720.excel.todaysummary.com.msn.com

      #1; Fri, 23 May 2008 07:51:00 GMT