Reference a call's value (as a criteria) in VBA?

[Sarah52181]

Hello All,

I'd like to use an AutoFilter macro that references a certain cell as it's criteria. I realize the below code is nowhere near correct, but hopefully it helps to demonstrate what I'm trying to do. I have the criteria as an "Array" because cell C1 contains text along the lines of "January", "February", "July", (etc.) It concatenates the values of other cells based on which checkboxes a user selects. Hopefully the end result will be Excel auto-filtering based on the months the user has selected.

Sub month_macro()
 
ActiveSheet.Range("A14:S300").AutoFilter Filed:=16, Criteria1:=Array("C1"), Operator:=xlFilterValues
End Sub

Is it possible to reference a cell's value in code?

Thanks for any help you can offer!

 

[Firefly2012]

Hi

You need your C1 cell to contain a string value (which can be formula based) like:

January, February, March, April, May

You could then write the code like this:

With ActiveSheet
  .Range("A14:S300").AutoFilter Filed:=16, Criteria1:=Split(.Range("C1").Value), Operator:=xlFilterValues
End With

Note that the filter field (ie column 16) can't contain actual Excel dates as you are filtering on a string value (month name), not a date

 

[Sarah52181]

Thank you! C1 does currently contain a string value (which is formula based). I did have the string set to include quotation marks around each month as that's usually how I write it when I'm using an array criteria (multiple selections) for an autofilter macro. I'm not sure if I'll still need the quotation marks or not given that your string value didn't contain them!

With that said, I tried the code you provided and got a "run-time" error. "application-defined or object-defined error"

Any thoughts as to what I'm doing wrong?

 

[Norie]

I kept on getting that error when I tried this and I found out the problem, eventually.

You've mispelt Field, you have Filed.

It was only when I recorded a macro and compared it's code to the code I copied from your post that I noticed.

Only took me about 1½ hours.

 

[Sarah52181]

HA! I literally don't think I would have ever noticed that. Thank you for finding it!!! The code runs now, however it only filters on the last criteria. For example, if cell C1 says:
January, March, July

It only filters on July. Is there a way to tweak it so that it works similiar to the array criteria I've used in the past?

 

[Norie]

It worked for me, here's the code I used

Dim arrFilter
 
   arrFilter = Split(ActiveSheet.Range("C1").Value, ",")

   ActiveSheet.Range("A14:S300").AutoFilter Field:=16, Criteria1:=arrFilter, Operator:=xlFilterValues

 

[Sarah52181]

This is PERFECT! Thank you!