Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with arrays
Hi, I have some operations that i think VB has some easier way to accomplish
or may be a function: 1)The sum of all the array elements 2)Get just the array elements different of "" or 0 3)Get juste the array elements that they are not repeated (i mean arr1(1,1,2,z,z,3,3,4,4,4,1,1,4,5) to arr1(1,2,3,4,5,z) TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with arrays
The easy one first:
#1: Dim myArr As Variant myArr = Array(1, 1, 2, "z", "z", 3, 3, 4, 4, 4, 1, 1, 4, 5) MsgBox Application.Sum(myArr) #2. I think that you'll have to loop through the array. Option Explicit Sub testme02() Dim myArr1 As Variant Dim myArr2() As Variant Dim iCtr As Long Dim eCtr As Long myArr1 = Array(1, 1, 2, "z", "z", 3, 3, 4, 4, 4, 1, 1, 4, 5) eCtr = 0 For iCtr = LBound(myArr1) To UBound(myArr1) If Application.IsNumber(myArr1(iCtr)) Then eCtr = eCtr + 1 ReDim Preserve myArr2(1 To eCtr) myArr2(eCtr) = myArr1(iCtr) End If Next iCtr If eCtr = 0 Then MsgBox "No elements" Else For iCtr = LBound(myArr2) To UBound(myArr2) MsgBox iCtr & "--" & myArr2(iCtr) Next iCtr End If End Sub #3. You can build the array, but check using application.match(). If that returns an error, then the value isn't in the second array. Option Explicit Sub testme03() Dim myArr1 As Variant Dim myArr2() As Variant Dim iCtr As Long Dim eCtr As Long myArr1 = Array(1, 1, 2, "z", "z", 3, 3, 4, 4, 4, 1, 1, 4, 5) ReDim myArr2(1 To 1) myArr2(1) = myArr1(1) eCtr = 1 For iCtr = LBound(myArr1) + 1 To UBound(myArr1) If IsError(Application.Match(myArr1(iCtr), myArr2, 0)) Then eCtr = eCtr + 1 ReDim Preserve myArr2(1 To eCtr) myArr2(eCtr) = myArr1(iCtr) End If Next iCtr For iCtr = LBound(myArr2) To UBound(myArr2) MsgBox iCtr & "--" & myArr2(iCtr) Next iCtr End Sub There are lots of other techniques. You could use a Collection or a Dictionary object. If you want to try the collection, you may want to look at John Walkenbach's site: http://j-walk.com/ss/excel/tips/tip47.htm His routine has an option sort, too. filo666 wrote: Hi, I have some operations that i think VB has some easier way to accomplish or may be a function: 1)The sum of all the array elements 2)Get just the array elements different of "" or 0 3)Get juste the array elements that they are not repeated (i mean arr1(1,1,2,z,z,3,3,4,4,4,1,1,4,5) to arr1(1,2,3,4,5,z) TIA -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with arrays
filo666 wrote:
Hi, I have some operations that i think VB has some easier way to accomplish or may be a function: 1)The sum of all the array elements 2)Get just the array elements different of "" or 0 3)Get juste the array elements that they are not repeated (i mean arr1(1,1,2,z,z,3,3,4,4,4,1,1,4,5) to arr1(1,2,3,4,5,z) TIA If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook For 2) myArr = Application.Transpose(myArr) myArr = ArrayRowFilter1(ArrayRowFilter1(myArr, 1, "", "<"), 1, 0, "<") myArr = Application.Transpose(myArr) for 3) myArr = ArrayUniques(myArr, , "1horiz", False) Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with arrays as arguments | Excel Worksheet Functions | |||
SUMPRODUCT with 3 arrays not working | Excel Worksheet Functions | |||
Working with Arrays | Excel Worksheet Functions | |||
Excel2000: Need help on UDF (working with arrays) | Excel Programming | |||
working on 2 different worksheets....arrays? | Excel Programming |