I've been putting together an excel spreadsheet that uses dynamically-named lists. It works pretty well, but the magnitude
of the spreadsheet is getting to a point where doing this through the cells is becoming a bit problematic. I'm looking for
ways that I can use VBA to create the list for me, but I'm having some trouble finding relevant commands.
two worksheets: RawData and ReferenceData
RawData consists of a series of delimeted text, and I am interested in
the contents of column B. I cannot guarantee that the series wil be sorted. The data of interest starts in Row 5, and
extends for an uncertain number of rows (I could paste different data there with more or fewer rows). However, the last row
of applicable data will be blank.
So, out of the whole sheet of data, the "important" parts might look
[RawData]B5 => "test1"
[RawData]B6 => "test3"
[RawData]B7 => "test2"
[RawData]B8 => "test4"
[RawData]B9 => blank
ReferenceData consists of a several columns of data against which we will be checking the
contents of RawData. For convenience, consider that the column I want to check the RawData against (this time) is column G.
Each reference column could vary in length though (column A might only have 5 entries, but column G could have 12). This
data is sorted. Again, the last cell in a column of the applicable reference data will be blank.
[ReferenceData]G1 => "test1"
[ReferenceData]G2 => "test2"
[ReferenceData]G3 => blank
I would like the macro to kick in automatically whenever something on RawData
changes (i.e., I've pasted new data). When activated, the macro should go down RawData cell by cell, and see if the value
lies within a particular ReferenceData column. If so, the RawData cell value gets added to List. If not, the RawData cell
value skips to the next cell.
So, after the macro runs, I should have a list, that I can refer to elsewhere (say,
in data validation, or used with other lists merged into a single new one). List, then, would be "test1, test2"
And so I present a teaching opportunity I know how to describe what I want done, but need some guidance on the