Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array question | Excel Discussion (Misc queries) | |||
another array question | Excel Worksheet Functions | |||
array question | Excel Programming | |||
Array question | Excel Programming | |||
Is this an array question? | Excel Programming |