Excel VBA - Creating a recordset that is filtered

upobDaPlaya
upobDaPlaya used Ask the Experts™
on
I have a tab called Source.  Within VBA I currently have

Set wsSource = Sheets("Source")
x = wsSource.Range("A1").CurrentRegion.Value

For i = 2 to Ubound(x,1)
......
........

The issue is I now realize that I should only be running the above code (just a sample above) if the records in the source tab are approved.  There is a field called Approved where user put yes/no.  Thus, how do I change my code so that x reflects only Approved records.  I assume I need to create a recordset for x ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi,

Please upload your sample workbook or full code.
David L. HansenCEO

Commented:
I'd use a VLookUP function to grab what's in column A based on what the value is in your "Approved" column.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
I'd use "yes" or "no" as the lookup_value, and your approval column as the range.
A good example is here.

You may be able to do all of what you need with out any vba code at all.
NorieAnalyst Assistant

Commented:
You could apply a filter to only return the 'records', ie rows, that have 'Yes' in the 'Approved field', ie column.
Commented:
Just check that field as you are iterating through the records..

For i = 2 to Ubound(x,1)
    If LCase(x(I, 2)) = "yes" Then 'modify field to suit
        'do your thing
    End If
Next

Open in new window

upobDaPlaya

Author

Commented:
Perfect...thx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial