Databases Reference
In-Depth Information
Dim I As Integer
Dim strt As Integer, noc As Integer
For I = 1 To Len(addr$)
If Mid$(addr$, I, 1) = "C" Then
strt = I + 1
noc = Len(addr$) - I
End If
Next I
FindCol = Val(Mid$(addr$, strt, noc))
End Function
This function simply finds the “C” in the R1C1 string and returns the numeric value of all the
characters following the “C” (which is the column number). Again, this function is extremely useful
when used in combination with the
FindPos
function in a manner such as this:
FindCol(FindPos(ActiveWorkbook.Name, Activesheet.name, "B",
True, False))
1.8
COPYING, CLEARING, AND DELETING DATA
The entire discussion up to this point has been preparing the reader for this topic, copying data.
Before data in a Worksheet can be copied, it must be (1) located utilizing the find command and
(2) selected using some variation of the Range method.
Copying can take the form of one of two methods. The first and most obvious would be to
copy a cell or range of Cells from one section of a Worksheet to another section of a Worksheet.
The second would be to copy values contained in Cells of a Worksheet into variables defined within
the VBA program, or vice versa. Oftentimes, an array will be utilized to store a section of Worksheet
cell values. The values are most often read into the array by means of a looping mechanism. Some
examples will be given to show the reader how to accomplish both forms of copying. The Workbook
shown in Figure 1.8 will be utilized for the examples in the text.
Looking at Figure 1.8, there are three data series in an 8-row
12-column format. Suppose it
is desired to read the information from the first data series into an array. This can be accomplished
in the following manner. First, a Public variable should be declared at the top of the module to
hold the data as follows:
×
Public WellPos(8, 12) As Single
The data in this spreadsheet is from a 96 well microplate, so the variable is named
WellPos
(for well
position), and the first index 8 denotes rows, whereas the second index 12 denotes columns (as is the
case with matrices). Therefore,
WellPos
(2,7) should contain the data in row 2 column 7 of the first
data series, which in this case is 3.63. It is a fairly easy process to read the information from the Cells
into an array using a looping structure, but before that can be done, a “landmark” must be found that
denotes the start of the first data series. Looking at Figure 1.8, notice that the letter “A” is one column
to the left of the starting column of each data series and also denotes the starting row of each data
series. More importantly, notice that a capital “A” is not found on the Worksheet prior to cell A4.
Therefore, searching for the start of the first data series, it is possible to use “A” as the search string
because it is unique prior to the start of the first data series. The following subroutine will locate the
start of the first data series and write the values from each cell into the Public variable
WellPos
.
Sub ReadIntoArray()
'Reads Well Values from "CopyExample.xls" Worksheet into Array
