A Variable list of variables

[JacobA]

Greetings all,

I have a report that I am attempting to modify to allow the end user have more input into running it. I am importing data from a SQL Database and filtering and massaging it to get it into the correct format. Some items need a different multiplier used to achieve the desired result and this list can grow (or theoretically shrink). Setting up a sheet for the user to input the flags (variables) and the values used isn't a problem but I don't see a way to have my macro use a variable list of variables.

I'm sure there is a way, but I certainly don't see it and I could use some suggestions.

Thanks

Jake

 

[Luke M]

A little hazy on which variables are changing by user, and which need to be calculated. If user is filling in variables into cells, why not have the code read the cells?
myVariable = Range("A2").Value

If there are downstream variables that need to be calculated, you could either have some cells with formulas performing a lookup, and again, read the cells, or do something like:

Select Case myVariable
Case 1
newVar = "first choice"
Case 2
newVar = "second choice"
Case 3
newVar = "third choice"
Case Else
'If it's not any of the above
newVar = "bad choice"
End Select

 

[JacobA]

The problem as I see it is that the number of variables can change. I am probably making this harder than it needs to be. If the last row is 10 I can set the code to get 10 different variables, but next month there might be 12 and I don't know how to get that value to change.

 

[Luke M]

You could build a collection. Store as many variables as you are given in the collection, then you can use it's properties to see how many variables you have and sitll access them all.

Example of dealing with collection.

Sub VariableInputs()
Dim lastRow As Long, i As Long
Dim c As Range
Dim myCol As Collection
Set myCol = New Collection

'How many cells have values?
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Build collection
    i = 1
    For Each c In .Range("A1:A" & lastRow).Cells
        myCol.Add c.Value, CStr(i)
        i = i + 1
    Next c
End With

'Example of working with collections
MsgBox "Number of variables stored: " & myCol.Count
MsgBox "You chose the value of: " & myCol(InputBox("Which value do you want?", "Choose", 1))

       
End Sub

 

[JacobA]

Thanks Luke! I knew there had to be a way to get that count into the code, but I have never dealt with collections before. Looks like a great new learning experience!

Jake