Dim errorString As String
Dim errorFlag As Boolean
Sub GetErDone()
Dim fileName, cCode, FILE_PATH, jobName As String
Dim cCodeCounter, maxCells, jobCounter As Integer
'On Error GoTo Handler
Application.ScreenUpdating = False 'removes screen flicker, vastly improves performance
jobCounter = 11 'user input of job numbers starts here
FILE_PATH = "[URL="file://\\Centraln\Public\labs"]\\Centraln\Public\labs[/URL] shared\DWTS Shared\Analytical summary_Preservation\Saved Analytical Summaries\"
jobName = Cells(jobCounter, 1)
fileName = FILE_PATH & jobName & ".xlsx"
Workbooks("TestBook.xlsm").Sheets(2).Activate
maxCells = Cells(Rows.Count, 1).End(xlUp).Row
Do 'Loop on jobs
errorFlag = False
errorString = vbNullString
Dim wb As Excel.Workbook
Set wb = GetObject(fileName)
'Handler:
'If Err.Number = 432 Then
' fileName = FILE_PATH & jobName & ".xls"
'End If
'Resume Next
cCodeCounter = 18 'AS template starts listing analytes here
Workbooks("TestBook.xlsm").Sheets(2).Activate
Do 'loop on ccode
ActiveCell = wb.Sheets(1).Cells(cCodeCounter, 9)
cCode = Left(ActiveCell, 5)
Workbooks("TestBook.xlsm").Sheets(2).Activate
For i = 5 To maxCells
If cCode = vbNullString Then
Exit For
End If
If cCode = "C2023" Then 'BNA Scan is always combined with BNA and is unnecessary info
Exit For
End If
If cCode = Cells(i, 6) Then
Cells(i, 8) = 1
Exit For
End If
If i = maxCells Then
errorFlag = True 'if the c-code isn't found, displays error on final sheet
errorString = errorString & cCode & ", "
End If
Next i
cCodeCounter = cCodeCounter + 1
Loop While cCode <> vbNullString
Set wb = Nothing
Call AddSheet(jobName)
Call FillNewSheet(jobName)
'reset sheet 2 after each job
Workbooks("TestBook.xlsm").Sheets(2).Activate
For i = 5 To maxCells
Cells(i, 8).ClearContents
Next i
jobCounter = jobCounter + 1
ActiveWorkbook.Sheets(1).Activate
jobName = Cells(jobCounter, 1)
fileName = FILE_PATH & jobName & ".xlsx"
Loop Until jobName = vbNullString
Application.ScreenUpdating = True
End Sub
Sub FillNewSheet(sheetName As String)
Dim rowCount As Integer 'counter
Dim maxRow As Integer 'sets upper bound for the for loop
Dim nextRow As Integer 'row counter for insertion into sheet2
Dim totalVolume, totalVOC, totalGC, totalHPLC, totalLCMS, totalRADS As Integer
Dim totalEXT, totalWETCHEM, totalWAREHOUSE, totalTOC, totalMETALS As Integer
Dim totalEXPOSURES, totalDWTS, boolGC1, boolGC2, boolVOC1, boolVOC2, boolVOC3 As Boolean
Dim countHPLC, countVOC, total125, total250, total500, total1000 As Integer
Dim totalLabels As Integer
boolGC1 = True
boolGC2 = True
boolVOC1 = True
boolVOC2 = True
boolVOC3 = True
totalEXPOSURES = False
totalDWTS = False
total125 = 0
total250 = 0
total500 = 0
total1000 = 0
countHPLC = 0
countVOC = 0
totalLabels = 0
totalVOC = 0
totalGC = 0
totalHPLC = 0
totalLCMS = 0
totalRADS = 0
totalEXT = 0
totalWETCHEM = 0
totalWAREHOUSE = 0
totalTOC = 0
totalMETALS = 0
nextRow = 4 'first row based on the preset formatting
Workbooks("TestBook.xlsm").Sheets(2).Activate
maxRow = Cells(Rows.Count, 1).End(xlUp).Row 'returns bottom most non-empty cell in first column
For rowCount = 5 To maxRow
If Cells(rowCount, 8) <> 0 Then 'If user selected this row, copies the row then pastes into sheet 2
Worksheets(2).Cells(rowCount, 1).Select
ActiveCell.EntireRow.Select
Selection.Copy
Sheets(sheetName).Select
Cells(nextRow, 1).Select
ActiveSheet.Paste
nextRow = nextRow + 1
Sheets(2).Select
If Cells(rowCount, 7) = "LCMS" Then
totalLCMS = totalLCMS + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
ElseIf Cells(rowCount, 7) = "RADS" Then
totalRADS = totalRADS + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
ElseIf Cells(rowCount, 7) = "EXT" Then
totalEXT = totalEXT + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
ElseIf Cells(rowCount, 7) = "TOC" Then
totalTOC = totalTOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
ElseIf Cells(rowCount, 7) = "METALS" Then
totalMETALS = totalMETALS + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
ElseIf Cells(rowCount, 7) = "WAREHOUSE" Then
totalWAREHOUSE = totalWAREHOUSE + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
ElseIf Cells(rowCount, 7) = "WETCHEM" Then
totalWETCHEM = totalWETCHEM + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
ElseIf Cells(rowCount, 7) = "EXPOSURES" Then
totalEXPOSURES = True
ElseIf Cells(rowCount, 7) = "DWTS" Then
totalDWTS = True
totalVolume = totalVolume + 150
'HPLC and divisions
ElseIf Cells(rowCount, 7) = "HPLC" And countHPLC = 0 Then
If Cells(rowCount, 5) = "Y" Then
totalHPLC = totalHPLC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
Else
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
countHPLC = 1
totalHPLC = totalHPLC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
End If
ElseIf Cells(rowCount, 7) = "HPLC" And 15 > countHPLC And 0 < countHPLC Then
If Cells(rowCount, 5) = "Y" Then
totalHPLC = totalHPLC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
Else
countHPLC = countHPLC + 1
End If
ElseIf Cells(rowCount, 7) = "HPLC" And countHPLC >= 15 Then
If Cells(rowCount, 5) = "Y" Then
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
totalHPLC = totalHPLC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
Else
countHPLC = 1
totalHPLC = totalHPLC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
End If
'GC and divisions
ElseIf Cells(rowCount, 7) = "GC" Then
totalGC = totalGC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
ElseIf Cells(rowCount, 7) = "GC-1" And boolGC1 = True Then
boolGC1 = False
totalGC = totalGC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
ElseIf Cells(rowCount, 7) = "GC-2" And boolGC2 = True Then
boolGC2 = False
totalGC = totalGC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
If Cells(rowCount, 3) = 250 Then
total250 = total250 + 1
ElseIf Cells(rowCount, 3) = 125 Then
total125 = total125 + 1
ElseIf Cells(rowCount, 3) = 500 Then
total500 = total500 + 1
Else
total1000 = total1000 + 1
End If
'VOC and divisons
ElseIf Cells(rowCount, 7) = "VOC" And countVOC = 0 Then
If Cells(rowCount, 5) = "Y" Then
totalVOC = totalVOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
Else
countVOC = 1
totalVOC = totalVOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
End If
ElseIf Cells(rowCount, 7) = "VOC" And 0 < countVOC And countVOC < 6 Then
If Cells(rowCount, 5) = "Y" Then
totalVOC = totalVOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
Else
countVOC = countVOC + 1
End If
ElseIf Cells(rowCount, 7) = "VOC" And countVOC >= 6 Then
If Cells(rowCount, 5) = "Y" Then
totalVOC = totalVOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
Else
countVOC = 1
totalVOC = totalVOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
End If
ElseIf Cells(rowCount, 7) = "VOC-1" And boolVOC1 = True Then
boolVOC1 = False
totalVOC = totalVOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
ElseIf Cells(rowCount, 7) = "VOC-2" And boolVOC2 = True Then
boolVOC2 = False
totalVOC = totalVOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
ElseIf Cells(rowCount, 7) = "VOC-3" And boolVOC3 = True Then
boolVOC3 = False
totalVOC = totalVOC + Cells(rowCount, 2)
totalLabels = totalLabels + Cells(rowCount, 2)
totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
End If 'end of bottle addition
End If 'for loop
Next rowCount
'Display totals at bottom of new sheet
Sheets(sheetName).Select
nextRow = nextRow + 2
Cells(nextRow, 1).Value = "TOTALS"
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total Volume:"
Cells(nextRow, 2).Value = totalVolume
Cells(nextRow, 3).Value = "mL"
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total VOC:"
Cells(nextRow, 2).Value = totalVOC
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total TOC:"
Cells(nextRow, 2).Value = totalTOC
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total HPLC:"
Cells(nextRow, 2).Value = totalHPLC
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total GC:"
Cells(nextRow, 2).Value = totalGC
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total Metals:"
Cells(nextRow, 2).Value = totalMETALS
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total LCMS:"
Cells(nextRow, 2).Value = totalLCMS
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total RADS:"
Cells(nextRow, 2).Value = totalRADS
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total Extractions:"
Cells(nextRow, 2).Value = totalEXT
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total Wetchem (non-TOC):"
Cells(nextRow, 2).Value = totalWETCHEM
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total Number of Bottles to be shipped:"
Cells(nextRow, 2).Value = totalWAREHOUSE
If totalEXPOSURES = True Then
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "CONTACT EXPOSURES ABOUT RVCM!"
Cells(nextRow, 2).Value = 1
totalLabels = totalLabels + 1
End If
If totalDWTS = True Then
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "**MAKE SURE TO TAKE A pH READING!**"
Cells(nextRow, 2).Value = 1
totalLabels = totalLabels + 1
End If
nextRow = nextRow + 2
If total1000 > 0 Then
Cells(nextRow, 1).Value = "Total 1000 mL Amber:"
Cells(nextRow, 2).Value = total1000
nextRow = nextRow + 1
End If
If total500 > 0 Then
Cells(nextRow, 1).Value = "Total 500 mL Amber:"
Cells(nextRow, 2).Value = total500
nextRow = nextRow + 1
End If
Cells(nextRow, 1).Value = "Total 250 mL Amber:"
Cells(nextRow, 2).Value = total250
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total 125 mL amber:"
Cells(nextRow, 2).Value = total125
nextRow = nextRow + 1
Cells(nextRow, 1).Value = "Total Labels:"
Cells(nextRow, 2).Value = totalLabels
nextRow = nextRow + 1
If errorFlag = True Then
Cells(nextRow, 1).Value = "The following C-codes were not recognized: "
Cells(nextRow, 4).Value = errorString
End If
'Removes copied data from clipboard
Application.CutCopyMode = False
End Sub
Sub AddSheet(sheetName As String)
ActiveWorkbook.Sheets("Template").Copy _
after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = sheetName
Cells(1, 1) = sheetName
End Sub