VBA Excel Return values from an array with loop

Question

I have a small loop in my vba function, which gives me for each Month - i.e. Parameter - the respective contract from a list and works for every month except December. Contractlist is a vertical array (3,5,7,9,12). This is the code:

k = 1
Do Until Month(Parameter) < contractlist(k, 1)
k = k + 1
Loop

nextcontract = MonthName(contractlist(k, 1))

Here is the problem: When the month is December, month(December) is never smaller than contractlist(k, 1) because 12 is the smallest value in that array. In that case, I would like to just jump to the first value of the array, i.e. "3". In other words, in the last line nextcontract should be MonthName(contractlist(1, 1)). Can somebody give me a hint how to do that?

Thanks, your help is really appreciated!

SeanC · Accepted Answer · 2015-04-14 22:00:45Z

2

There's a sneaky way to cheat that uses MOD
As mod will reduce a value to 0 when it equals the value looked for (and multiples), we can use this so the Month when you get December from your parameter will be zero, instead of 12...

Do Until (Month(Parameter) Mod 12) < contractlist(k, 1)

This will leave every other month alone, but will return 0 for December.

answered Apr 14, 2015 at 22:00

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

c b · Accepted Answer · 2015-04-15 00:57:29Z

0

I understand better now what you're doing. I've edited my solution below. Don't be afraid to use more variables. Sometimes you can be using one variable to do too much and it makes it hard to see a solution.

' My test data
Dim contractlist(1 To 5, 1 To 1) As Integer
contractlist(1, 1) = 3
contractlist(2, 1) = 5
contractlist(3, 1) = 7
contractlist(4, 1) = 9
contractlist(5, 1) = 12

Dim month As Integer ' represents Month(Parameter)
month = 12

' Solution
Dim contractCount As Integer
Dim nextContractNum As Integer
Dim nextcontract As String
contractCount = UBound(contractlist, 1)

' Next contract is the first in the list
' if we don't find a later one
nextContractNum = contractlist(1, 1)

For i = 1 To contractCount
    If month < contractlist(i, 1) Then
        nextContractNum = contractlist(i, 1)
        Exit For
    End If
Next i

nextcontract = MonthName(nextContractNum)

edited Apr 15, 2015 at 0:57

answered Apr 14, 2015 at 21:37

c b

1588 bronze badges

3 Comments

StefanW Over a year ago

Thank you! I tried this, but the return of the function is still #VALUE! . It seems like k does not get a value if month(parameter) = 12. But it still works for every other month...

c b Over a year ago

I see now. It makes things easier if you loop through the number of contract months you have, rather than all the months in the calendar. It also helps to separate your "answer" month and your "loop counter" into separate variables.

c b Over a year ago

(Sean's solution is interesting but fails if you have contract months (3, 5, 7) and a parameter month of 10.)

Collectives™ on Stack Overflow

VBA Excel Return values from an array with loop

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related