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

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)

---

Hi Bryan,

How does one go about setting a variable?

Thanks,

SamuelT

Originally Posted by SamuelT

Hi Bryan,

How does one go about setting a variable?

Thanks,

SamuelT

Please Login or Register  to view this content.

trying this

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

replace sheet1 with the name of your sheet

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

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.

Thanks for all the suggestions guys! Everything seems to be in working order. Phew!

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