Solved: Variable as AutoFilter Criteria

[ajrob]

I'm using Excel 2000 (old school), and am wondering if it is possible to use a variable as a filtering criteria in AutoFilter.

Here's an example of what I wrote, but it doesn't work...

[VBA]Sub CurrMo_Bookings()
' Applies AutoFilter to DataXfr worksheet for Bookings.
Application.ScreenUpdating = False
' Clears all Filters
Call Clear_DataXfr_Filters

' Filters for Bookings
Dim CurrDate As Integer

CurrDate = InputBox("What is the current date (YYmm)?", "User Input")
Worksheets("DataXfr").Activate
Selection.AutoFilter Field:=23, Criteria1:=CurrDate

Range("B3").Select
End Sub[/VBA]

This is a bit crude, since the MessageBox comes up every time. I was just toying with it to see if I could get the AutoFilter to run properly.

DataXfr is a sheet where I load up all the data from various sheets in the workbook. When I ran the code, it filtered the date field for CurrDate, not 0911 (as would be the case now).

Thanks

[RolfJ]

You declared CurrDate as Integer. Therefore if you were to enter '0911' in the InputBox VBA would interpret it as 911. What format does Field 23 actually have? Date, number or string?

[mdmackillop]

Dates are fiddly, can you post a simple sample of the data?

[ajrob]

Here's a copy of some of the data in Field 23:

BookMonth0611031205040603060605050701

The column is formatted as 0000, which I presume could be interpreted as an Integer. This was necessary in my setup as the YYMM format would be 912, instead of the desired 0912 for this month.

So I ran the code again, and Field 23 had a custom value = 911

I guess my question has now changed. How would you re-write my code such that the variable carries a format of 0000 to match the values in the target column?

[ajrob]

Sorry, my post was a column when I wrote it, but a buch of gibberish after I posted it. Here's what it looks like, with "Book Month" as the column header.

Book
Month
0611
0312
0504
0603
0606
0505
0701

[mdmackillop]

Can you post a sample workbook. I don't know if your dates are numbers, proper dates or even text.

[ajrob]

Can't post the workbook. Column is formatted as a custom number, 0000.

How do you force a format of 0000 on a variable?

[mdmackillop]

[VBA] CurrDate = InputBox("What is the current date (YYmm)?", "User Input")
CurrDate = Format(CurrDate, "0000")
[/VBA]

[ajrob]

Thanks so much ... this worked!