Excel AutoFilter to AutoIT

[Juvigy]

So here is what i want to do as in VBA;

ActiveSheet.Range("$A$1:$I$536").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(3, "1/0/1900 0:59:0", 4, "1/0/1900 1:0:0")

I tried several things but cant get that to work with the array.

For example this doesnt work:

$Array[0]=3
$Array[1]="1/0/1900 0:59:0"
$Array[2]=4
$Array[3]="1/0/1900 1:0:0"
$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,$Array)

The filter works if i do:

$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,"1/0/1900 1:0:0")

But sellects only one value. I would like to have all values between "1/0/1900 0:00:1" and "1/0/1900 1:00:0"

[JoHanatCent]

So here is what i want to do as in VBA;

ActiveSheet.Range("$A$1:$I$536").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(3, "1/0/1900 0:59:0", 4, "1/0/1900 1:0:0")

I tried several things but cant get that to work with the array.

For example this doesnt work:

$Array[0]=3
$Array[1]="1/0/1900 0:59:0"
$Array[2]=4
$Array[3]="1/0/1900 1:0:0"
$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,$Array)

The filter works if i do:

$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,"1/0/1900 1:0:0")

But sellects only one value. I would like to have all values between "1/0/1900 0:00:1" and "1/0/1900 1:00:0"

So maybe:

$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,"1/0/1900 0:00:1"&" AND "&"1/0/1900 1:0:0")

[Juvigy]

It doesnt work. Maybe because the formatting is messing it.

@Edit

Fixed it.As the column contains timedifference values like:0.01 0.04 0.03 0.02 0.03 0.03

I was formatting it as "m/d/yyyy h:mm" to be able to manually filter easily.

I calculated that "1/0/1900 1:0:0" is "0.0416666666860692" so i did:

$oExcel.ActiveSheet.Range("A:I").AutoFilter(9,"<= 0.0416666666860692",1,">= 0") which did the trick.

But i would like to know why this VBA worked and how i can translate it to AutoIt:

Criteria:=Array(3, "1/0/1900 0:59:0", 4, "1/0/1900 1:0:0")

I got it by recording a macro and filtering values manually.

Edited July 11, 2011 by Juvigy

[PsaltyDS]

I'm not finding an easy reference to the that syntax in VBA or VBScript, and it's not one I've had to use before: "Criteria:=Array()"

What does the ":=" operator signify?

Sorry if that's a stupid question.

[bogQ]

I'm not finding an easy reference to the that syntax in VBA or VBScript, and it's not one I've had to use before: "Criteria:=Array()"

What does the ":=" operator signify?

Sorry if that's a stupid question.

http://blog.contextures.com/archives/2010/12/15/excel-autofilter-with-criteria-in-a-range/#axzz1RoQssChT

Edited July 11, 2011 by bogQ

[smartee]

@PsaltyDS: By no means is that a stupid question, at first glance, from by background of pascal I blurted out in my head "assignment operator "

However, after some research, I realized that ":=" has a specialized meaning in VBA, it is used as a named argument operator, suited for cases where in passing parameters to functions, you want to specify exactly which parameters you wish to set, irrespective of order.

Here is a page with some examples: http://www.cpearson.com/excel/DefinedNames.aspx

[PsaltyDS]

@PsaltyDS: By no means is that a stupid question, at first glance, from by background of pascal I blurted out in my head "assignment operator "

However, after some research, I realized that ":=" has a specialized meaning in VBA, it is used as a named argument operator, suited for cases where in passing parameters to functions, you want to specify exactly which parameters you wish to set, irrespective of order.

Here is a page with some examples: http://www.cpearson.com/excel/DefinedNames.aspx

Ah so, very helpful and I can see that's a useful feature too!

So the first thing for @Juvigy to try is to make sure all the parameters are submitted in the correct order, because AutoIt doesn't support the parameter naming syntax. The example being translated from VBA is not concerned with correct parameter order because it uses the parameter names. For AutoIt you can't do that, so you must pass them in correct order.

Edit: The correct parameter order per MSDN (at least for Excel 2007): .AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

Edited July 11, 2011 by PsaltyDS

[Juvigy]

Hi Psalty,

I tried that and it works if i use single string value for the criteria. But if i Try to convert this:

ActiveSheet.Range("$A$1:$I$536").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(3, "1/0/1900 0:59:0", 4, "1/0/1900 1:0:0")

It doesnt work. And i dont understand where 3 and 4 came from in the array (1 and 3 aelemnts).

The VBA example is recorded by me when manually selecting. I did 2 clicks selecting with one click many values (there is a plus like in explorer tree) and one click can select many. Here is a small screenshot: