Control and Manipulation of Worksheet Data - Automated Data Analysis Using Excel - page 107

'Determine and Write In Number of Measurements
Workbooks(ExtractBook).Worksheets(ActiveSheet.Name).Cells(Results
Row, 3) = "[No.]"
'Loop Structure to Determine number of measurements
ptrRow = RawDataRow + 1: LoopCounter = 1
While
Workbooks(ExtractBook).Worksheets(ActiveSheet.Name).Cells(ptr
Row, 1).Value <> ""
'Integrity Check - Only Add Data if Samples are IDENTICAL
If
Workbooks(ExtractBook).Worksheets(ActiveSheet.Name).Cells
(ResultsRow + LoopCounter, 1) = _
Workbooks(ExtractBook).Worksheets(ActiveSheet.Name).Cells
(RawDataRow + LoopCounter, 1) Then
Workbooks(ExtractBook).Worksheets(ActiveSheet.Name).Cells
(ResultsRow + LoopCounter, 3) = _
LastColinRowX((ExtractBook), (ActiveSheet.Name), (ptrRow)) - 2
Else
MsgBox
Workbooks(ExtractBook).Worksheets(ActiveSheet.Name).Cells(Results
Row + LoopCounter, 1) & _
" <> " &
Workbooks(ExtractBook).Worksheets(ActiveSheet.Name).Cells(RawData
Row + LoopCounter, 1), _
vbExclamation + vbOKOnly, "Samples Don't Match!!! Data Not
Extracted"
End If
ptrRow = ptrRow + 1: LoopCounter = LoopCounter + 1
Wend
Some degree of explanation is in order for the foregoing routine. First, the function LastColinRow
is utilized to determine the number of measurements. In the [RawData] section, the number of
measurements for each sample will be the number of columns populated for a particular sample's
row minus two. Two is subtracted because the first two columns in this section hold the sample
name and a baseline (denoted B) in the Worksheet.
It is also important to look at the looping structure utilized to access and acquire the data.
While “hard limits” could be established using the locations determined by the landmarks, it is far
simpler to do the following. Because each section of the report is separated by an empty row, a
While loop can be utilized that starts looping at the section of interest ([RawData]) and continues
looping until a blank cell is encountered. Notice that ptrRow is initially set to equal RawDataRow + 1
because the samples begin one row below the landmark [RawData]. The variable LoopCounter
simply keeps track of how many times the loop has cycled, which, when utilized with other landmark
locations, can determine data locations to be extracted or written to.