Offset and Merged cells

[Jim]

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.

 

[Jim]

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.

 

[Sandy]

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]

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