Ideas for pulling and storing multiple values from data source

**Lewis2010** · 07-13-2011, 02:33 PM

Hey, I havent quite hammered out the code Im going to use yet but just wanted to see if anyone had any ideas. Im looking for a way to store values that meet a certain criteria in order to use them later as a variable in another part of my macro. The data is being pulled from a SQL server in the first part of the macro.

Sample Data:
Source Number
JE 1234
AR 5236
AR 5236
AR 5478

I would be looking to store the numbers 5236 and 5478 based on the criteria Source= "AR". As shown in the sample data there will be duplicate numbers and I only want to store one instance of it. Later in the macro I will be using each number as a condition for calculation/lookup.

Im not looking for exact code, instead maybe any ideas people want to contribute. Thanks in advance!

**davegugg** · 07-13-2011, 02:39 PM

Can you change the SQL to only pull distinct numbers?
Other than that, you can used the advanced filter to only give you unique entries.

**Lewis2010** · 07-13-2011, 02:50 PM

Yea Im going to try both of those. The thing I really wasnt sure about was in there a way to store the values in an array and later reference that array as a single variable and loop through it performing and operation for each value.

**davegugg** · 07-13-2011, 03:02 PM

If you are running sql, you probably have a reference to ADO or DAO. If that is true, you should be able to use a recordset. You can load a recordset using SQL and loop through each element using recordset functions.

**Lewis2010** · 07-13-2011, 03:05 PM

I'll have to check that out thanks Dave! I'm new to VBA and SQL Im a finance and accounting guy who has been teaching myself when I have time so I appreciate all the input.