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

 

[msb1977]

I will implement that code tonight and test to see if it works. Thanks for your time! (both of you)

 

[Norie]

You could either redim the array after the loop, use a dynamic array or load the data directly into an array without looping.

Here's skywriter's code adapted to use the latter.

Sub Test()
Dim wbBudget              As Workbook
Dim wbOSR                  As Workbook
Dim Criteria_Proj As Variant
Dim rngData As Range, r As Range, c As Long

    With wbBudget.Worksheets("Filter Lookup")
        Set rngData = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
    End With
    
    Criteria_Proj = Application.Transpose(rngData.Value)

 

[skywriter]

Judging by the OP's comment about blanks being filtered, I was under the impression there might be blanks in column B which I why I used special cells and constants, so as not to loop through blank cells.

I was under the impression you couldn't load a non-contiguous range directly into an array and in testing your code with some blanks here and there it doesn't.

What am I missing?

You could either redim the array after the loop, use a dynamic array or load the data directly into an array without looping.

Here's skywriter's code adapted to use the latter.

Sub Test()
Dim wbBudget              As Workbook
Dim wbOSR                  As Workbook
Dim Criteria_Proj As Variant
Dim rngData As Range, r As Range, c As Long

    With wbBudget.Worksheets("Filter Lookup")
        Set rngData = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
    End With
    
    Criteria_Proj = Application.Transpose(rngData.Value)

 

[Norie]

Bruce

As far as I can tell if that was the case then the original loop would have terminated when the first blank was encountered.

 

[skywriter]

Okay, yeah I misunderstood or I would have just written it in directly like you did.

I don't really understand what Wend means, I don't use it.

 

[Norie]

I think it's an extraction of Whlle End and indicates the end of a While loop.

 

[hiker95]

I don't really understand what Wend means, I don't use it.

skywriter,

If you go into the VBA Editor, and, in the box in the upper right hand corner where it says Type a question for help, enter Wend, and, scroll thru the list for examples on how to use Wend.

 

[skywriter]

Thanks Norie and hiker95.

 

[hiker95]

skywriter,

You are welcome. Glad we could help.

 

[msb1977]

Thanks guys. I couldn't get the more simple code to work right. Will try again. But, skywriter's code works perfectly. I really appreciate your input/help!

A couple more questions...sorry for my ignorance, but I just started learning VBA 2 weeks ago
1. What do the variables "c" and "r" define?
2. Skywriter said "This assumes you do NOT has an Option Base 1 statement at the top of your module" - what is an Option Base 1 statement?

Here is the finished section of code. I do a few more things afterwards with the data as well.

Sub Test()

'Define Variables
Dim wbOSR               As Workbook   'Obligation_Status_Report workbook (from server)
Dim wbBudget            As Workbook   'Budget & Execution Tool workbook (active file)
Dim Criteria_BFY()      As String     'Defines fiscal year criteria
Dim Criteria_Proj()     As String     'Defines project criteria
Dim Criteria_Task()     As String     'Defines task criteria
Dim rngBFY              As Range      'Defines range of fiscal year criteria
Dim rngProj             As Range      'Defines range of project criteria
Dim rngTask             As Range      'Defines range of task criteria
Dim c1                  As Long
Dim c2                  As Long
Dim c3                  As Long
Dim r1                  As Range
Dim r2                  As Range
Dim r3                  As Range

'Open the Obligation Status Report from the sharepoint server
Set wbBudget = ActiveWorkbook
Set wbOSR = Workbooks.Open("https://abc\Obligation_Status_Report.xlsx")

'Set lookup data criteria for fiscal year
With wbBudget.Sheets("Filter Lookup")
   Set rngBFY = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
End With

ReDim Criteria_BFY(rngBFY.Cells.Count - 1)
c1 = 0

For Each r1 In rngBFY
   Criteria_BFY(c1) = r1.Value
   c1 = c1 + 1
Next r1

'Filter Obligation Status Report for the fiscal year
wbOSR.Sheets(1).Range("Table1").AutoFilter Field:=2, Criteria1:=Criteria_BFY, Operator:=xlFilterValues
   
'Set lookup data criteria for projects
With wbBudget.Sheets("Filter Lookup")
   Set rngProj = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
End With

ReDim Criteria_Proj(rngProj.Cells.Count - 1)
c2 = 0

For Each r2 In rngProj
   Criteria_Proj(c2) = r2.Value
   c2 = c2 + 1
Next r2

'Filter Obligation Status Report for projects
wbOSR.Sheets(1).Range("Table1").AutoFilter Field:=6, Criteria1:=Criteria_Proj, Operator:=xlFilterValues

'Set lookup data criteria for tasks
With wbBudget.Sheets("Filter Lookup")
   Set rngTask = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
End With

ReDim Criteria_Task(rngTask.Cells.Count - 1)
c3 = 0

For Each r3 In rngTask
   Criteria_Task(c3) = r3.Value
   c3 = c3 + 1
Next r3

'Filter Obligation Status Report for tasks
wbOSR.Sheets(1).Range("Table1").AutoFilter Field:=7, Criteria1:=Criteria_Task, Operator:=xlFilterValues
   
'Copy and Paste the specified range from the OSR workbook to the OSR DAI worksheet
wbOSR.Sheets(1).Range("Table1").SpecialCells(xlCellTypeVisible).Copy
wbBudget.Sheets("OSR DAI").Range("I2").PasteSpecial xlPasteAll
Application.CutCopyMode = False
wbOSR.Close SaveChanges:=False