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.

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.

The reason why it's not reading is because the value is not in the
offset cell you're referencing. Try this codein a blank workbook,
insert this code into a new module and it will explain a lot more
"visually" then me trying to type it :-)

Sub Test()
Dim i As Integer, TheCell As String
On Error Resume Next
Range("A1").Value = "Cell A1"
Range("B1").Value = "Cell B1"
Range("C1").Value = "Cell C1"
Range("D1").Value = "Cell D1"
Range("E1").Value = "Cell E1"
Range("A2").Value = "-5"
Range("B2").Value = "-4"
Range("C2").Value = "-3"
Range("D2").Value = "-2"
Range("E2").Value = "-1"
MsgBox "Take a look the cells are " & _
"now filled with there information, hit the " & _
"OK button after the error occurs"
Range("B1:E1").Merge

AfterError:
Range("F1").Select
For i = -1 To -5 Step -1
If ActiveCell.Offset(0, i) = Empty Then
TheCell = "Empty"
Else
TheCell = ActiveCell.Offset(0, i).Value
End If
MsgBox ActiveCell.Offset(0, i).Address & _
"'s value is now- " & " " & TheCell & Chr(13) _
& "It's offset is ActiveCell.Offset(0," & i & ")"
Next i
End Sub


Sandy


Jim wrote:
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.

Thanks, Sandy

I eventually got the code to read the merged cell next door to the active
cell but still had trouble with the unmerged cell two cells over which
should have been ...offset(0,2) by trying different numbers for the offset.
The number 4 works. Why I don't know but I thoroughly tested it a hundred
times without an error so will go with that.

Thanks again
Jim


"Sandy" wrote in message
oups.com...
The reason why it's not reading is because the value is not in the
offset cell you're referencing. Try this codein a blank workbook,
insert this code into a new module and it will explain a lot more
"visually" then me trying to type it :-)

Sub Test()
Dim i As Integer, TheCell As String
On Error Resume Next
Range("A1").Value = "Cell A1"
Range("B1").Value = "Cell B1"
Range("C1").Value = "Cell C1"
Range("D1").Value = "Cell D1"
Range("E1").Value = "Cell E1"
Range("A2").Value = "-5"
Range("B2").Value = "-4"
Range("C2").Value = "-3"
Range("D2").Value = "-2"
Range("E2").Value = "-1"
MsgBox "Take a look the cells are " & _
"now filled with there information, hit the " & _
"OK button after the error occurs"
Range("B1:E1").Merge

AfterError:
Range("F1").Select
For i = -1 To -5 Step -1
If ActiveCell.Offset(0, i) = Empty Then
TheCell = "Empty"
Else
TheCell = ActiveCell.Offset(0, i).Value
End If
MsgBox ActiveCell.Offset(0, i).Address & _
"'s value is now- " & " " & TheCell & Chr(13) _
& "It's offset is ActiveCell.Offset(0," & i & ")"
Next i
End Sub


Sandy


Jim wrote:
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.