+ Reply to Thread
Results 1 to 9 of 9

Open Ended VBA Criteria

  1. #1
    Valued Forum Contributor
    Join Date
    09-23-2005
    Location
    Bristol, UK
    MS-Off Ver
    2007
    Posts
    664

    Open Ended VBA Criteria

    Hi all,

    I'm putting together a search facility in my spreadsheet and am trying to extend some VBA I've been using so far in a much simpler search/filter set-up. Essentially, what I want to do is instead of having a specific criteria such as:

    Selection.AutoFilter Field:=10, Criteria1:="Katherine Hatch"

    I want Excel to search whatever is in a specific cell (this will be determined by some OFFSET/Data Validation), so the VBA code would look like:

    Selection.AutoFilter Field:=10, Criteria1:="A1"

    The above doesn't seem to work - it's looking for the text "A1" as opposed to whatever is in A1. Can someone tell me what I need to change?

    TIA,

    SamuelT

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by SamuelT
    Hi all,

    I'm putting together a search facility in my spreadsheet and am trying to extend some VBA I've been using so far in a much simpler search/filter set-up. Essentially, what I want to do is instead of having a specific criteria such as:

    Selection.AutoFilter Field:=10, Criteria1:="Katherine Hatch"

    I want Excel to search whatever is in a specific cell (this will be determined by some OFFSET/Data Validation), so the VBA code would look like:

    Selection.AutoFilter Field:=10, Criteria1:="A1"

    The above doesn't seem to work - it's looking for the text "A1" as opposed to whatever is in A1. Can someone tell me what I need to change?

    TIA,

    SamuelT
    Does it work if you set the .value of A1 in a variable, and use the variable? (no quotes)

    ---

  3. #3
    Valued Forum Contributor
    Join Date
    09-23-2005
    Location
    Bristol, UK
    MS-Off Ver
    2007
    Posts
    664
    Hi Bryan,

    How does one go about setting a variable?

    Thanks,

    SamuelT

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by SamuelT
    Hi Bryan,

    How does one go about setting a variable?

    Thanks,

    SamuelT
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor funkymonkUK's Avatar
    Join Date
    01-07-2005
    Location
    London, England
    Posts
    500
    trying this

    Selection.AutoFilter Field:=10, Criteria1:=worksheets("sheet1").range("a1").value

    replace sheet1 with the name of your sheet

  6. #6
    Valued Forum Contributor
    Join Date
    09-23-2005
    Location
    Bristol, UK
    MS-Off Ver
    2007
    Posts
    664
    Hi,

    Thanks for all your input so far. Perhaps I should of shown you this before. The full piece of VBA is as below. As well as searching for "Katherine Hatch" (to be replaced by "A1", I've also got a password unprotect and reprotect thing going on. Also I don't know if the first line makes the Criteria1:=worksheets("sheet1") part of the code suggested by funkymonk redundant as I've already stated it before.

    Sub KH()

    Sheets("Components").Visible = True
    Sheets("Capacity").Visible = True
    Sheets("Billing").Visible = True
    Sheets("Extra Fees Calculator").Visible = True
    Sheets("Control").Visible = True

    Sheets("Programme (2 Week)").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=10, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    Sheets("Programme (High Level)").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=10, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    Sheets("Capacity").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=33, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    Sheets("Components").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=3, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    Sheets("Billing").Select
    ActiveSheet.Unprotect Password:="donottouch"
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=3, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Extra Fees Calculator").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=3, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Programme (2 Week)").Select

    End Sub

    Thanks guys,

    SamuelT

  7. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by SamuelT
    Hi,

    Thanks for all your input so far. Perhaps I should of shown you this before. The full piece of VBA is as below. As well as searching for "Katherine Hatch" (to be replaced by "A1", I've also got a password unprotect and reprotect thing going on. Also I don't know if the first line makes the Criteria1:=worksheets("sheet1") part of the code suggested by funkymonk redundant as I've already stated it before.

    Sub KH()

    Sheets("Components").Visible = True
    Sheets("Capacity").Visible = True
    Sheets("Billing").Visible = True
    Sheets("Extra Fees Calculator").Visible = True
    Sheets("Control").Visible = True

    Sheets("Programme (2 Week)").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=10, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    Sheets("Programme (High Level)").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=10, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    Sheets("Capacity").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=33, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    Sheets("Components").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=3, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    Sheets("Billing").Select
    ActiveSheet.Unprotect Password:="donottouch"
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=3, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Extra Fees Calculator").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=3, Criteria1:="Katherine Hatch"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Programme (2 Week)").Select

    End Sub

    Thanks guys,

    SamuelT
    Would it not have been easier to say
    Please Login or Register  to view this content.
    and use that, similarly
    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor
    Join Date
    09-23-2005
    Location
    Bristol, UK
    MS-Off Ver
    2007
    Posts
    664
    Thanks for all the suggestions guys! Everything seems to be in working order. Phew!

  9. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by SamuelT
    Thanks for all the suggestions guys! Everything seems to be in working order. Phew!
    Good to see, and thanks for the response
    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1