Reading Merged Cells

[Noob McKnownowt]

Hey guys,

And yes before the warnings come in i know working with merged cells is
difficult to say the least but there is no way around this one and my own
attempts to solve the problem have resulted in epic fails.

Anyhow, i have a workbook and in this workbook i have a sheet set up with
days of the week across the top and names down the left, i.e.


A B C D E
| mon | tue | wed | thur
name 1 in out off
--------------------------------------------
name 2 in in off
--------------------------------------------
name 3 off

OK not the best diagram but it will do, each column has been assigned its
own range i.e. RngMon = sheet1.range("B2:B4") so it can be read on its
particular day.

Rows 2 and 3 show 2 people in varying states during the week, the 3rd row
shows someone who is in 1 state for the entire week, this is shown by merging
all the cells on the row and centering the content, the only problem is, is
that when it comes to this particular cell the counter i have set up misses
the info in the meged cell.

I think this is because the actual text was entered in cell B4 (mon) and i
am trying to count it in cell D4 (wed) and obviously that cell doesnt hold a
value.

Does anyone know how to have a piece of code search a merged area? instead
of just reading the cell it is given, if it hits a cell thats part of a group
of merged cells then it will search all the cells in the merged area?

Any assistance would be greatly appreiciated.

Cheers,

The Noob.

 

[Joel]

When you merge cells all the data is in the 1st cell (upper left cell). It
may appear that the text is in other locations with formating, but the data
is really in the first cell. Your search range must include the 1st cell to
find the data.

 

[Noob McKnownowt]

So there is no way of searching a merged area? you have to go to the first
cell in the group? thats going to make this a bit more difficult.

cheers bud, much appreiciated.

The Noob.

 

[Peter T]

Does anyone know how to have a piece of code search a merged area? instead
of just reading the cell it is given,

Say B24 is merged but you want to look in C3. Obviously C3 will be empty
and the only cell in the merged area that can contain data is B2.

to get the data
myVal = Range("C3").MergeArea(1)

and if you need to know
mergedAddress = Range("C3").MergeArea.Address

Regards,
Peter T

 

[Noob McKnownowt]

Thanks for that guys, very much apprieciated, cheers.

The Noob.