Databases Reference
In-Depth Information
'Place the Average on hidden Worksheet AVG and the STDEV on
hidden Worksheet STDEV
Dim ii As Integer
Dim row As Integer, col As Integer, cellstr$, funcstr$
Sheets(SGDataSheet).Activate
For col = 0 To 11
For row = 0 To 7
For CellPos = 1 To UtilScans
If
IncludePoint
(CellPos,
row + 1, col + 1, ((RowOrigin +
row + (CellPos - 1) * 10)), (ColOrigin + col)) = True _
Then
Select Case CellPos
Case UtilScans 'Omit ","
cellstr$ = cellstr$ & SGDataSheet & "!" & Chr(ColOrigin
+ 64 + col) & _
Trim(str((RowOrigin + row + (CellPos - 1) * 10)))
Case Else
cellstr$ = cellstr$ & SGDataSheet & "!" & Chr(ColOrigin
+ 64 + col) & _
Trim(str((RowOrigin + row + (CellPos - 1) * 10))) & ","
End Select
End If
Next CellPos
funcstr$ = "=AVERAGE(" & cellstr$ & ")"
Call AddFormula(CalcBook, "ORAVG", ((row + 8)), ((col + 2)),
funcstr$)
'Report Number of Cells (Datapoints) used in revised average
Worksheets("ORAVG").Cells(row + 20, col + 2).Value =
TotalPts(row + 1, col + 1)
funcstr$ = "=STDEV(" & cellstr$ & ")"
Call AddFormula(CalcBook, "ORAVG", ((row + 32)), ((col + 2)),
funcstr$)
'If ALL Values in LOT Not within 6 sigma limits color cell
For CellPos = 1 To UtilScans
If WithinSixSigma("ORAVG", row + 1, col + 1, (RowOrigin +
row + (CellPos - 1) * 10), ColOrigin + col, CellPos) =
False Then
Call ColorCell(CalcBook, "ORAVG", Chr(64 + col + 2) &
Trim(str(row + 8)))
Exit For
End If
Next CellPos
'Clean Up built strings for next pass
cellstr$ = "": funcstr$ = ""
Next row
