vba array question

  • Thread starter Thread starter chick-racer
  • Start date Start date
C

chick-racer

I would like to know if i can use an array for this problem....

I am picking out certain numbers from a set of data. I dont know how
many numbers the program will pick out at any given time (the numbers
it pick have to meet certain criteria), I would like to store these
numbers it picks out in perhaps an array, so that i can later do
average and standard deviation on them

ie) cells(6,"J")=Application.WorksheetFunction.Average(***what do i
type?***)
and

cells(6,"K")=Application.WorksheetFunction.StDev(**dont know how to add
array)

Note** i have never dealt with arrays before so if someone could tell
me how to declare and store the numbers in it, that would be awesome!
thanks Alot!
 
Dim varr as Variant

varr = Array(1,4,5,3,8,10,2,2,1,5,3,7)
j = 3
k = 4
Cells(6,j).Value = Application.Average(varr)
Cells(6,k).Value = Application.stdev(varr)

Since you don't show your code, I couldn't hazard a guess on how to build an
array in your code.
 
yes that is entirely true... Thank you for your help.. i have used your
ideas to make this short one... but it doesnt seem to be working
yet....


here is what i've done so far....

Dim rng As Range
Dim c As Range
Dim count As Integer
Dim newMean As Double
Dim meanArray() As Double
Dim arraySize As Integer
count = 1

Set rng = Range("A1:F1") ' I have much bigger range that this will be
filtering through.. just using this to test right now.

For Each c In rng

v = c.Value

If (IsEmpty(v) = False) And IsNumeric(v) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
'arraySize = count

meanArray(count) = v

count = count + 1

End Select

End If
Next c


newMean = Application.WorksheetFunction.Average(meanArray)

Cells(1, "I") = newMean

End Sub
 
Sub MakeMean()
Dim rng As Range
Dim c As Range
Dim count As Integer
Dim newMean As Double
Dim meanArray() As Double
Dim arraySize As Integer
count = 0

Set rng = Range("A1:F1")

ReDim meanArray(1 To rng.count)

For Each c In rng

v = c.Value

If Not IsEmpty(v) And IsNumeric(v) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
'arraySize = count
count = count + 1
meanArray(count) = v
End Select

End If
Next c

ReDim Preserve meanArray(1 To count)

newMean = Application.WorksheetFunction.Average(meanArray)

Cells(1, "I") = newMean
End Sub

Worked for me.
 
Back
Top