Solved

# MS Excel - working with Arrays and looping through code

Posted on 2006-07-18
178 Views
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
Question by:smehle
• 2

LVL 92

Accepted Solution

Patrick Matthews earned 250 total points
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

LVL 9

Expert Comment

0

Author Comment

Thank you both, this was driving me NUTS!
0

LVL 92

Expert Comment

You're welcome :)
0

## Featured Post

### Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…