Will someone please explain this code - only a few lines?

[msb1977]

Hi all

I'm a novice with VBA and am trying to understand what this section of code means and what it actually does. I know the end result is that it is used to set the range for filtering from a list.

Dim wbBudget              As Workbook 
Dim wbOSR                  As Workbook  
Dim Criteria_Proj(100)   As String    
Dim FirstRow                As Integer    

FirstRow = 1
While wbBudget.Sheets("Filter Lookup").Cells(FirstRow + 1, 2) <> ""
   Criteria_Proj(FirstRow) = wbBudget.Sheets("Filter Lookup").Cells(FirstRow + 1, 2)
   FirstRow = FirstRow + 1
Wend

This part I don't need explained, but am including for reference.

wbOSR.Sheets(1).Range("Table1").AutoFilter Field:=6, Criteria1:=Criteria_Proj, Operator:=xlFilterValues

 

[skywriter]

If you have no blanks in range "rngBFY" you should use the code Norie gave you.

My code loops through the range adding it to the array, Norie's code puts it all into the array in one shot.

When you declare an array this way:
Dim Criteria_BFY(100)

It is a 0 based array, meaning the first element in the array is 0 not 1.

So the elements are from 0 to 100, there are 101 elements in the array.

If you put the Option Base 1 statement at the top of the module then the array becomes a 1 based array meaning there are 100 elements in the array 1 to 100.

It does not work with all arrays, some stay base 0 regardless of the statement, I'll let you educate yourself on that one.

c is a counter to put the elements in the array and as you can see I started it at 0, I would have changed this if you had the Option Base 1 statement.

 

[msb1977]

Thanks for the help skywriter. I am going to try to get Norie's code to work also. You guys have a good weekend.

 

[msb1977]

Norie's code works perfectly too...I had this

Dim Criteria_Proj

instead of this

Dim Criteria_Proj()

 

[msb1977]

One final question
On 2 reports I filter on specific projects and tasks.

On a 3rd report I filter on everything beginning with "2P_", "X3_", etc.

I put 2P_*, X3_*, etc in another column and it breaks Norie's code from above. Is there another qualifier I need to add like quotations or when it creates the array it just won't recognize the "*" as denoting everything beginning with...

Thanks again!