Solved: Storing a range as a variable?

[Knud]

Dear all Excel superusers ,

I have a challenge that I hope some of you people could help me with. The challenge is as follows:

I need to look up a value in a range. So far so good, and I have found the function "Application.vlookup(arg1, arg2, arg3, arg4)"

And this works for a specified range, but my challenge is, that my "range" is dynamic, meaning it depends on which month I'm looking at - e.g. for january the range should be "A1:B3", while the range should be "A4:B6" for february etc. My current code looks like the following:

ProjectMonthTotal = Application.VLookup(cellvalue, Sheets("Sheet1").Range(CurrentRange), 2, False)
'ProjectMonthTotal is the variable that should contain the value I'm looking for
'cellvalue is a variable containing the value I want to look up
'CurrentRange is the part that doesn't work, I don't know how to set this one up, so it will look up in the correct range each time

I'm open for all ideas, this challenge is killing me

I have a variable "Month" that always represents the month that is being calculated.

I'm using Excel 2007.

Please help me

The lost Excel ranger

[CreganTur]

One option: you could create a series of Select Case statements to choose the range based on the month.

[VBA]SELECT CASE month
Case January
myRange = "A1:B3"
Case February
myRange = "A4:B6"
'fill in cases for all months
End Select[/VBA]

[Slyboots]

Vlookup is used to retrieve a single value from a table. Instad, use the SUMIF function. If you have the number of the month stored in column A, for example, you'd use this as a formula, where Month is a range name for the cell containing the current month:

=SUMIF(A1:A25,Month,B1:B25)

If works the same way in VBA, with a bit of modification:

Application.WorksheetFunction.SumIf(Range("A1:A25"),Month,Range("B1:B25"))

S

[Knud]

Dear Randy and Slyboots,

Thank your for your feedback. I tried the CASE-statement, but I didn't get it to work. How should define the variables? as "Range"?

However inspired by Slyboots, I did a workaround and created a table with unique names (which I at first didn't think was possible). But then I could solve it with the vlookup-function.

Thank you both for your quick and good replies. And Randy, I would really like to learn more about the CASE-statement, can you recommend where I can read more about this? (I haven't been able to find it in the Excel help-function).

/Knud

[mikerickson]

You could use Offset
[VBA]set arg2 = Range("A1:B3").Offset(3*(Month(keyMonth)-1),0)
Rem more code
myValue = Application.vlookup(arg1, arg2, arg3, arg4)[/VBA]this assumes that keyMonth is an Excel date, but similar syntax could be used for different forms of the keyMonth.

[MaximS]

Why not to use dynamic named ranges:

Menu >> Insert >> Name >> Define

plus Validation list

plus function =sum(indirect(C1))

See attached for details.

[Knud]

Thank you, guys, your help has been fantastic, and I have solve my problem. I just had a new problem, but MaximS solution seem to solve this for me.

Thank you.

Knud