How to find MIN, MAX, AVERAGE in array results using VBA??
Behnfeldt
I copy the information to a "Printout" page, in case the user would like to
print the results. This also gives me an easy way to find the MAX, MIN, and
AVERAGE of Range "T" (or column "G"). I then display this in another
listbox on the userform.
The problem is, this seems to take too long. The users will be using this
array for up to two hundred items to review results on, one after another
(arrowing down from a list). Copying to the Printout page each time takes
too long. I can fix this by giving them an option button, which would THEN
copy the array only when selected to print (as very few will probably
actually be printed).
But doing this, I will lose my MAX, MIN, and AVERAGE of Range "T", which I
still want to display as each item is selected. This is very valuable
information and I would like to use VBA to calculate each directly, rather
than copying each time. These would then be displayed in the userform (a
listbox?).
If Str(vArr(x, 1)) = Str(UserForm2.ComboBox1.Value) Then
MyArray(0, Nr) = Range("A" & x)
MyArray(1, Nr) = Range("B" & x)
MyArray(2, Nr) = Range("J" & x)
MyArray(3, Nr) = Range("H" & x)
MyArray(4, Nr) = Range("F" & x)
MyArray(5, Nr) = Range("G" & x)
MyArray(6, Nr) = Range("T" & x)
'write to "Printout" to print
'Sheets("Printout").Select
'Range("A" & Nr + 6).FormulaR1C1 = MyArray(0, Nr)
'Range("B" & Nr + 6).FormulaR1C1 = MyArray(1, Nr)
'Range("C" & Nr + 6).FormulaR1C1 = MyArray(2, Nr)
'Range("D" & Nr + 6).FormulaR1C1 = MyArray(3, Nr)
'Range("E" & Nr + 6).FormulaR1C1 = MyArray(4, Nr)
'Range("F" & Nr + 6).FormulaR1C1 = MyArray(5, Nr)
'Range("G" & Nr + 6).FormulaR1C1 = MyArray(6, Nr)
'Sheets("Temp").Select
Nr = Nr + 1
End If
Next x
UserForm2.ListBox2.Column() = MyArray
Any help greatly appreciated!
Denny
behn...@bright.net
Myrna Larson
>I have the results of the following array displayed in a userform listbox.
>I copy the information to a "Printout" page, in case the user would like to
>print the results. This also gives me an easy way to find the MAX, MIN, and
>AVERAGE of Range "T" (or column "G"). I then display this in another
>listbox on the userform.
>
>The problem is, this seems to take too long. The users will be using this
>array for up to two hundred items to review results on, one after another
>(arrowing down from a list). Copying to the Printout page each time takes
>too long. I can fix this by giving them an option button, which would THEN
>copy the array only when selected to print (as very few will probably
>actually be printed).
>
>But doing this, I will lose my MAX, MIN, and AVERAGE of Range "T", which I
>still want to display as each item is selected. This is very valuable
>information and I would like to use VBA to calculate each directly, rather
>than copying each time. These would then be displayed in the userform (a
>listbox?).
You can call the worksheet functions from your VBA code. You don't have to
write the data to a worksheet first.
Assuming you have put the address of the range of interest into a string
variable called RngAddr
Dim RngAddr As String
Dim Rng As Range
Dim Avg As Double
Dim Min As Double
Dim Max As Double
'code here to set value of RngAddr
Set Rng = Range(RngAddr)
With Application.Worksheetfunction
Avg = .AVERAGE(Rng)
Min = .MIN(Rng)
Max = .MAX(Rng)
End With