CODEWITHSUNDEEP
arraysexcelvbaloops
StefanW
StefanW

Reputation: 23

VBA Excel Return values from an array with loop

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!

Upvotes: 0

Views: 1031

Answers (2)

c b
c b

Reputation: 158

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)

Upvotes: 0

SeanC
SeanC

Reputation: 15923

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.

Upvotes: 2

Related Questions