Remember Me?

Posted to microsoft.public.excel.programming
 [email protected] external usenet poster Posts: 1 Converting Excel Formula Arrays to Dynamic VB Array Code Help

Hi all,

Its been a while since i have been on here so VB getting a little rusty, anyway hoping one of you Guys will be able to use your vast knowledge to help me resolve the following.

I have created a spreadsheet to monitor the results of some testing i am doing, however only want to show the Maximum, minimum and average results of those results that have passed. Now as it states in the header i can achieve this via using the following excel formulas on the spreadsheet.

{=IF(MIN(IF(\$C:\$C="PASS",IF(\$A:\$A="VP",\$C:\$C)))=0, "ND",MIN(IF(\$C:\$C="PASS",IF(\$A:\$A="VP",\$C:\$C)) ))}

{=IF(MAX(IF(\$C:\$C="PASS",IF(\$A:\$A="VP",\$C:\$C)))=0, "ND",MAX(IF(\$C:\$C="PASS",IF(\$A:\$A="VP",\$C:\$C)) ))}

{=IF(MIN(IF(\$C:\$C="PASS",IF(\$A:\$A="VP",\$C:\$C)))+MA X(IF(IF(\$C:\$C="PASS",IF(\$A:\$A="VP",\$C:\$C)))=0,"ND" ,AVERAGE(IF(\$C:\$C="PASS",IF(\$A:\$A="VP",\$C:\$C))))}

ColA ColB ColC
VP 6.5 PASS
VP 5.8 PASS
VP 1078 FAIL
VP 260 FAIL
VP 117 FAIL
VP 115 FAIL
VP 224 FAIL
VP 15.2 PASS

The problem is that there are there are many different permatations due to the differing builds as well as the other condition that the test subject can be in (approx 50 permatations in all) so Excel does not have the memory capacity to achieve this, hence the reason i suspect using a dynamic array and VB code would be better as it is hoped that it can do the calculation, populate the spreadsheet with the value, erase the array and move onto the next calculation until all values are in.

Anyway as stated it has been since i played with VB, so in all honesty don't even know were to start, however think once i get sorted with the first set of calculations then i shoul be ok.

Thus any help would be greatly appreciated.

Posted to microsoft.public.excel.programming
 GS[_6_] external usenet poster Posts: 1,182 Converting Excel Formula Arrays to Dynamic VB Array Code Help

Perhaps something less complex and more 'maintenance friendly'...

Sub Test2()
Dim vDataIn, vDataOut(), n&, j&, k&
Dim lRows&, lCols&, s1\$

With Sheets("Sheet1")
lRows = .Cells(Rows.Count, 1).End(xlUp).Row
vDataIn = .Range("A2:C" & lRows)
End With 'Sheets("Sheet1")

s1\$ = "(IF((A2:A" & lRows & "=""" & vDataIn(n, 1) & """)*(C2:C" _
& lRows & "=""" & vDataIn(n, 3) & """),B2:B" & lRows & "))"
ReDim vDataOut(UBound(vDataIn) * UBound(vDataIn), 8)

For n = LBound(vDataIn) To UBound(vDataIn)
For j = LBound(vDataIn) To UBound(vDataIn)
k = k + 1
vDataOut(k, 1) = vDataIn(n, 1)
vDataOut(k, 2) = vDataIn(n, 3)

vDataOut(k, 3) = "Min"
vDataOut(k, 4) = Evaluate("=MIN" & s1)
vDataOut(k, 5) = "Max"
vDataOut(k, 6) = Evaluate("=MAX" & s1)
vDataOut(k, 7) = "Avg"
vDataOut(k, 8) = Evaluate("=AVERAGE" & s1)
Next 'j
Next 'n
lRows = UBound(vDataOut): lCols = UBound(vDataOut, 2)
Sheets("Sheet1").Range("G1").Resize(lRows, lCols) = vDataOut

End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Posted to microsoft.public.excel.programming
 GS[_6_] external usenet poster Posts: 1,182 Converting Excel Formula Arrays to Dynamic VB Array Code Help

@ Garry:
You can't initialize a string with variables when these are not yet
initialized. You will get a runtime error.The formula string must be
placed into the loop.

Yes, yes.., nice catch! I pasted in the wrong place...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Posted to microsoft.public.excel.programming
 GS[_6_] external usenet poster Posts: 1,182 Converting Excel Formula Arrays to Dynamic VB Array Code Help

corrected version...

Sub Test2()
Dim vDataIn, vDataOut(), n&, j&, k&
Dim lRows&, lCols&, s1\$

With Sheets(2)
lRows = .Cells(Rows.Count, 1).End(xlUp).Row
vDataIn = .Range("A2:C" & lRows)
End With 'Sheets("Sheet1")

ReDim vDataOut(UBound(vDataIn) * UBound(vDataIn), 8)
For n = LBound(vDataIn) To UBound(vDataIn)
s1\$ = "(IF((A2:A" & lRows & "=""" & vDataIn(n, 1) & """)*(C2:C" _
& lRows & "=""" & vDataIn(n, 3) & """),B2:B" & lRows & "))"

For j = LBound(vDataIn) To UBound(vDataIn)
k = k + 1
vDataOut(k, 1) = vDataIn(n, 1)
vDataOut(k, 2) = vDataIn(n, 3)

vDataOut(k, 3) = "Min"
vDataOut(k, 4) = Evaluate("=MIN" & s1)
vDataOut(k, 5) = "Max"
vDataOut(k, 6) = Evaluate("=MAX" & s1)
vDataOut(k, 7) = "Avg"
vDataOut(k, 8) = Evaluate("=AVERAGE" & s1)
Next 'j
Next 'n
lRows = UBound(vDataOut): lCols = UBound(vDataOut, 2)
Sheets("Sheet1").Range("G1").Resize(lRows, lCols) = vDataOut

End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Programming 20 October 24th 13 09:14 AM excelCPA Excel Discussion (Misc queries) 3 September 25th 09 06:40 PM PV Jefe Excel Programming 2 October 10th 06 09:30 PM Dave Excel Worksheet Functions 2 June 26th 06 06:16 AM Koos Excel Programming 2 January 10th 04 03:30 PM

All times are GMT +1. The time now is 09:49 AM. Copyright ©2004-2023 ExcelBanter.