Excel VB or Macro. Referencing a cell by its contents.

[arnezmando]

Hello,

I have a bunch of data that was very poorly organized at the start. Each excel file has ten sheets. In each sheet there is a certain range that I'm interested in . . . say C67 to E77. I'm writing a program to select that range and copy it into a new sheet, or better yet a new workbook starting with the next available cell (stack the tables from all of the sheets into a master table that contains all of the data from all of the sheets).

The problem however, is that the range isn't always exactly the same. It does always have the same dimensions but on one sheet its C67:E77, on the next sheet its C69:E79, etc. . . Anyway, this table that I'm interested in always has a title (a cell above it that says "table 1" and the data I'm interested in is always below this title cell.

ie. if the title cell is C65 then the data range is C67:E77. If the title cell is C67 then the data range is C69:E79.

Is there a way to specify a range by referencing THE CELL THAT CONTAINS "Table 1"?

ie.
desired range = (2 cells below THE CELL THAT CONTAINS "Table 1") : (12 cells below and two cells to the right of THE CELL THAT CONTAINS "Table 1").


Any help would be great. . . and will help us to combat disease in our world.

Thanks

 

[jayzhangsj]

This is what I use for a similar situation.

First put in the following formula in a cell in the target sheet:

=MATCH("Table 1",Rawdata!C:C, 0)

name it DataStart.

Use the following formulas to reference the data:

=INDIRECT("Rawdata!C"&(DataStart+2))

blah, blah.

OFFSET is another function you can use.

I also use FIND function from Macro.

Watch the fact that INDIRECT and OFFSET are a volatile functions.

Jay