We help IT Professionals succeed at work.

Excel VBA - Creating a recordset that is filtered

133 Views
Last Modified: 2017-04-05
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

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi,

Please upload your sample workbook or full code.
CERTIFIED EXPERT

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
CERTIFIED EXPERT

Commented:
You could apply a filter to only return the 'records', ie rows, that have 'Yes' in the 'Approved field', ie column.
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Perfect...thx