Help with Variables in array! Easy fix

[jordanburch]

Hey Guys,

I have the below. I just cant figure the syntax out yet. Seems like an easy fix. I just want it to take the value and -1.

ActiveSheet.Range("$A$1:$BA$10000").AutoFilter Field:=2, Criteria1:=Array( _
         (Right(thisworkbook.worksheets("Variables").Range("A10").value, 2)-2), (Right(thisworkbook.worksheets("Variables").Range("A10").value, 2)-1), (right(thisworkbook.worksheets("Variables").Range("A10").value, 2)), Operator:=xlFilterValues

the range("a10") is equal to fyi

202104 so basically it should filter for 02/2021, 03/2021, 04/2021 in the array. Any help is apprecaited! Jordan

 

[jordanburch]

btw im getting a
compile error argument in parmarray not named issue on the operator= part

 

[rlv01]

Your AutoFilter statement is too complex and thus difficult to troubleshoot. Try using some variables to help you understand what is going on. Example:

    Dim DVal As String, MVal As String, YVal As String
    Dim Month1 As String, Month2 As String, Month3 As String
    Dim MArray As Variant
   
    DVal = Range("A10") 'assumption is "202104" string value
    YVal = Left(DVal, 4) 'year
    MVal = Right(DVal, 2) 'month
   
    Month1 = Right("0" & Val(MVal) - 2, 2) & "/" & YVal
    Month2 = Right("0" & Val(MVal) - 1, 2) & "/" & YVal
    Month3 = MVal & "/" & YVal
   
    MArray = Array(Month1, Month2, Month3)
    ActiveSheet.Range("$A$1:$BA$10000").AutoFilter Field:=2, Criteria1:=MArray, Operator:=xlFilterValues

 

[jordanburch]

Your AutoFilter statement is too complex and thus difficult to troubleshoot. Try using some variables to help you understand what is going on. Example:

    Dim DVal As String, MVal As String, YVal As String
    Dim Month1 As String, Month2 As String, Month3 As String
    Dim MArray As Variant
  
    DVal = Range("A10") 'assumption is "202104" string value
    YVal = Left(DVal, 4) 'year
    MVal = Right(DVal, 2) 'month
  
    Month1 = Right("0" & Val(MVal) - 2, 2) & "/" & YVal
    Month2 = Right("0" & Val(MVal) - 1, 2) & "/" & YVal
    Month3 = MVal & "/" & YVal
  
    MArray = Array(Month1, Month2, Month3)
    ActiveSheet.Range("$A$1:$BA$10000").AutoFilter Field:=2, Criteria1:=MArray, Operator:=xlFilterValues

This worked great thank you so much!

Jordan