Need help: Finding and Extracting certain cells

  • Thread starter Thread starter dani
  • Start date Start date
D

dani

Hi everyone,

I have the following problem:

I have a file with hundreds of tables/spreadsheets. I want to extract
from each one of these tables the value of just one certain cell.
The cell position is specified by a (constant) heading and its
background is yellow. Unfortunately the column-number and the line
number of the specific cell I'm interested in changes from table to
table.
So now I need a little program that figures out which column my cell
is in (where the heading is), and which line it is (where is the
yellow colour) and then writes the found value into a new spreadsheet.

I am new to excel but have programming experience, so I don't need an
explicit program (I should be able to figure this out) but rather a
general idea how to approach this task.
Are there predefined functions I can use, should I first transform my
file with standard excel commands,...??

Thanks for the help,
daniel
 
Code is untested and may contain typos

dim rng as Range, rng1 as Range, rng3 as Range
Dim fAddr as String
set rng = Cells.Find("columnheader")
if not rng is nothing then
fAddr = rng.address
do
set rng1 = rng
do while rng1.Interior.ColorIndex <> 6
set rng1 = rng.offset(1,0)
if rng1.row = 65536 then exit do
Loop
if rng1.row <> 65536 then
if rng3 is nothing then
set rng3 = rng1
else
set rng3 = Union(rng3,rng1)
end if
end if
set rng = cells.FindNext(rng)
Loop while rng.Address <> fAddr
if not rng3 is nothing then
rng3.Select
end if
End if
 
Back
Top