smehle
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both, this was driving me NUTS!
You're welcome :)
https://www.experts-exchange.com/questions/21600633/EXCEL-COMBO-BOX-ON-EACH-LINE-WITH-DATA-POPULATED-ALREADY.html
https://www.experts-exchange.com/questions/21235774/Populate-a-combo-box-with-data-from-a-Excel-spreadsheet.html
http://support.microsoft.com/default.aspx?kbid=161598
https://www.experts-exchange.com/questions/20807248/Auto-Fill-Combo-Box-Using-VBA.html
https://www.experts-exchange.com/questions/21107767/Persistant-data-in-a-combo-box-embedded-in-a-worksheet.html
http://www.j-walk.com/ss/excel/tips/tip47.htm