Excel VBA - Creating a recordset that is filtered

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 ?

upobDaPlayaAsked: 2017-04-05

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 31

ShumsExcel & VBA ExpertCommented: 2017-04-05

Hi,

Please upload your sample workbook or full code.

LVL 15

David L. HansenCEOCommented: 2017-04-05

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.

LVL 41

NorieAnalyst Assistant Commented: 2017-04-05

You could apply a filter to only return the 'records', ie rows, that have 'Yes' in the 'Approved field', ie column.

LVL 48

Wayne Taylor (webtubbs)Commented: 2017-04-05

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial