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

Sarah52181

New Member
Joined
Mar 7, 2012
Messages
19
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.

Code:
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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:

Code:
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
 
Upvote 0
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?
 
Upvote 0
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.:eek:
 
Upvote 0
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?
 
Upvote 0
It worked for me, here's the code I used
Code:
Dim arrFilter
 
   arrFilter = Split(ActiveSheet.Range("C1").Value, ",")

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

Forum statistics

Threads
1,212,144
Messages
6,106,218
Members
448,006
Latest member
oreo2996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top