VBA to capture values between two numbers

[Chazzo]

Hi Everyone,

If I have a named range with values 1 thru 12 named "Months", and two separate Cells that ask for user input "Beg Month" and "End Month".....

How can I use VBA to capture those values and the values between these two numbers?

For example, if a user input the number 5 for the "Beg Month" and the number 7 for the "End Month", how can I use a variable to capture the numbers 5,6, and 7?

The "Months" named range is on sheet 2 B5:B16.

The "Beg Month" entry cell is on Sheet1 in cell C11, and the "End Month" is on Sheet1 in Cell C12.

Any suggestions on VBA methods would be much appreciated.

Thank you very much in advance.

 

[JoeMo]

Maybe something like this:

Sub Chazzo()
'assumes the named ranges "Beg_Month" and "End_Month" are scoped at workbook level
Dim ValuesBetween()
ReDim ValuesBetween(1 To Range("End_Month") - Range("Beg_Month") + 1)
For i = 1 To Range("End_Month") - Range("Beg_Month") + 1
    ValuesBetween(i) = Range("Beg_Month") - 1 + i
Next i
MsgBox Join(ValuesBetween, ", ")

End Sub

 

[Chazzo]

Awesome, thanks JoeMo!

I will give this a shot and report back the outcome....

 

[Chazzo]

Looks like this will work for me, thanks again JoeMo.

This newbie will now "do something" with each value which I think I can manage....

I appreciate your level of skill.

Cheers.

 

[JoeMo]

Looks like this will work for me, thanks again JoeMo.

This newbie will now "do something" with each value which I think I can manage....

I appreciate your level of skill.

Cheers.

You are welcome - thanks for the reply.