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

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
Asked:
smehle
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
smehleAuthor Commented:
Thank you both, this was driving me NUTS!
0
 
Patrick MatthewsCommented:
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now