• Status: Solved
• Priority: Medium
• Security: Public
• Views: 192

# MS Excel - working with Arrays and looping through code

I am trying to create an array from two values. For example, lets say cell A1 = 5 and B1 = 7. I want code that examines the two values, identifies the smallest, creates an array that counts from the smallest down to 0, while increasing the largest at the same time:

5   7
4   8
3   9
2   10
1   11
0   12

My code is not working, and I am coming up with some funny values. What am I doing wrong?  Here is my code:

Function createArray(cellA, cellB)

Dim MPos1, getMin, myNewArray(100, 2), rw, prw
Dim myArray(100, 2)

If cellA <= cellB Then
MPos1 = 1
getMin = cellA
Else
MPos1 = 2
getMin = cellB
End If

myNewArray(0, 1) = cellA
myNewArray(0, 2) = cellB

For rw = 1 To getMin

prw = rw - 1

If MPos1 = 1 Then myNewArray(rw, 1) = myNewArray(prw, 1) - 1 Else myNewArray(rw, 1) = myNewArray(prw, 1) + 1
If MPos1 = 1 Then myNewArray(rw, 2) = myNewArray(prw, 1) + 1 Else myNewArray(rw, 1) = myNewArray(prw, 2) - 1
rw = rw + 1

Next

createArray = myNewArray(rw - 1, 2)

End Function
0
smehle
• 2
1 Solution

Commented:
Hi smehle,

Function createArray(cellA As Long, cellB As Long)

Dim myArray() As Long
Dim First As Long
Dim Second As Long
Dim Counter As Long

If cellA <= cellB Then
First = cellA
Second = cellB
Else
First = cellB
Second = cellA
End If

ReDim myArray(0 To First, 0 To 1) As Long

For Counter = 0 To UBound(myArray, 1)
myArray(Counter, 0) = First - Counter
myArray(Counter, 1) = Second + Counter
Next

createArray = myArray

End Function

Regards,

Patrick
0

Yash Infinite Solutions Private LimitedCommented:
0

Author Commented:
Thank you both, this was driving me NUTS!
0

Commented:
You're welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.