My code contains this error I just caught, hard coding 1 instead of i.
VacTime(1, 1) = ActiveCell.Value instead of VacTime(i, 1) = ActiveCell.Value

This was a problem I hadn't yet reached since the code was not reading the
merged cell values. The code would not read the unmerged cell value either.

"Jim" wrote in message
et...
This really came out of left field for me.

I'm using Excel 2000.

I'm having trouble reading values from merged columns using the code
below.It reads the first merged cell ok then returns Empty for all the
rest. VacTime() is a 14x3 array.

This version treats merged cells as simply adjacent cells and doesn't
work.
For i = 1 To 14
VacTime(1, 1) = ActiveCell.Value
VacTime(1, 3) = ActiveCell.Offset(0, 2).Value
If ActiveCell.Offset(0, 1).Value = "" Then
Temp = ""
Else
Temp = ActiveCell.Offset(0, 1).Value - ActiveCell.Value + 1
End If
VacTime(1, 2) = Temp
ActiveCell.Offset(1, 0).Select
Next

This version takes into account the cells that were merged and doesn't
work.
For i = 1 To 14
VacTime(1, 1) = ActiveCell.Value
VacTime(1, 3) = ActiveCell.Offset(0, 6).Value
If ActiveCell.Offset(0, 3).Value = "" Then
Temp = ""
Else
Temp = ActiveCell.Offset(0, 3).Value - ActiveCell.Value + 1
End If
VacTime(1, 2) = Temp
ActiveCell.Offset(1, 0).Select
Next

I have seven columns merged as 3 cells, 3 cells and 1 cell unmerged on
each row. There are 14 rows altogether with no spaces between rows. For
example, cells D8, E8 and F8 are merged, G8, H8 and I8 are merged and cell
J8 is unmerged.

Date data (mm/dd/yyyy) is in column 1 and 2 in the merged cells and text
data is the unmerged cell.

I know I can widen the columns to accommodate the date info but when I
started this report I deliberately narrowed all the columns down to a 4
width so I'd have more entry options. A column width of 4 displays my date
data as ####. Now I have a bunch of stuff above the merged columns and
this stuff comes primarily from VBA code so widening the columns is not an
option since all refs would need to be changed. And, I don't understand
how to use tables as I've never used them and kind of feel that changing a
bunch of code to accommodate a table would be a nightmare.

I sure hope someone can help because I'm stumped. Thanks in advance for
any help you can offer.