vba array question
 

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!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

vba array question
 

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.

--
Regards,
Tom Ogilvy

"chick-racer" wrote in message
...

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!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

vba array question
 

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

vba array question
 

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.

--
Regards,
Tom Ogilvy





"chick-racer" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

vba array question
 

Thank you sooo much

you are great assistance!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/