Can't get data centred across pairs of cells...?
 

Hi folks.

Apologies for double-posting. I've asked this on the vb.generaldiscussion
group as well, since my app resides in VB6. Maybe Excel gurus will know a
little more about the behaviour I'm seeing.

I'm using VB with a reference to the Excel object model, so that my VB app
can build Excel workbooks 'on-the-fly'.

I have a sub for building a table. Code follows. I hope it's clear what I
intend it to do. (I've cut away some of the calls for simplicity - you don't
need to see the stuff for changing fonts and applying borders.)

Private Sub BuildP7Table(ByVal Sheet As Excel.Worksheet, ByVal TopRow As
Integer)
Dim strFormula As String
Dim intLoop As Integer

strFormula = "=IF(R[-2]C="""","""",IF(R[-1]C=""0"",""0%"",R[-2]C/R[-1]C))"

With Sheet
.Range("C" & CStr(TopRow + 1) & ":J" & CStr(TopRow +
2)).NumberFormat = "@"
.Range("C" & CStr(TopRow + 3) & ":J" & CStr(TopRow +
3)).NumberFormat = "0%"
For intLoop = 0 To 3
.Range("C" & CStr(TopRow + intLoop) & ":D" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("E" & CStr(TopRow + intLoop) & ":F" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("G" & CStr(TopRow + intLoop) & ":H" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("I" & CStr(TopRow + intLoop) & ":J" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
Next intLoop
.Range("A" & CStr(TopRow + 3) & ":B" & CStr(TopRow +
3)).HorizontalAlignment = xlCenterAcrossSelection
.Cells(TopRow, 3).FormulaR1C1 = "Number Waiting"
.Cells(TopRow, 5).FormulaR1C1 = "12 Weeks"
.Cells(TopRow, 7).FormulaR1C1 = "18 Weeks"
.Cells(TopRow, 9).FormulaR1C1 = "22+ Weeks"
.Cells(TopRow + 1, 1).FormulaR1C1 = .Name
.Cells(TopRow + 2, 1).FormulaR1C1 = "Grand Total (32 services)"
.Cells(TopRow + 3, 1).FormulaR1C1 = "%"
.Cells(TopRow + 3, 3).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 5).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 7).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 9).FormulaR1C1 = strFormula
End With

End Sub


OK, I run the app, and then open the workbook it just built. I pick one of
the worksheets, scroll down to where TopRow happened to be for that sheet,
and I find that although the 4 text strings written to cells in TopRow are
correctly centered (centred here in UK!), the other ranges I specified as
xlCenterAcrossSelection are not. If I put the cursor in cell (TopRow + 1,
3), and begin to type something, it appears centered across the range of
cells between that cell and (TopRow + 1, 10). In fact, I can do the same in
any cell on that row, up to column 10, and the selection seems to be between
the chosen cell and the cell in column 10. Actually, I don't need any
formatting for the cells in even-numbered columns, because they'll never be
visible. So why has the code formatted them? (I can hear you saying 'What
if the user selects one of those cells and enters something in it?'. He
won't. A later step in the same application comes along and populates
everything.)

I get the same behaviour in cells on row (TopRow + 2), but oddly, those on
row (TopRow + 3) work as I intended. So I've tried making sure all the cells
on TopRow + 1 and TopRow + 2 have the same format as TopRow + 3 before
setting the horizontal alignment, then putting them back to how I need them
afterwards. No joy. I also tried putting something into the offending cells
with Formular1c1 beforehand, then removing it afterwards. Nope, that didn't
work either. What's going on here?

Thanks for any assistance
Andy

Can't get data centred across pairs of cells...?
 

I can't find any help for XLCenterAcrossSelection, so I tried it this way

With .Range("C" & CStr(TopRow + intLoop) & ":D" & _
CStr(TopRow + intLoop))
.Merge
.HorizontalAlignment = xlCenter
End With

and it seemed to work.

"Andy" wrote:

Hi folks.

Apologies for double-posting. I've asked this on the vb.generaldiscussion
group as well, since my app resides in VB6. Maybe Excel gurus will know a
little more about the behaviour I'm seeing.

I'm using VB with a reference to the Excel object model, so that my VB app
can build Excel workbooks 'on-the-fly'.

I have a sub for building a table. Code follows. I hope it's clear what I
intend it to do. (I've cut away some of the calls for simplicity - you don't
need to see the stuff for changing fonts and applying borders.)

Private Sub BuildP7Table(ByVal Sheet As Excel.Worksheet, ByVal TopRow As
Integer)
Dim strFormula As String
Dim intLoop As Integer

strFormula = "=IF(R[-2]C="""","""",IF(R[-1]C=""0"",""0%"",R[-2]C/R[-1]C))"

With Sheet
.Range("C" & CStr(TopRow + 1) & ":J" & CStr(TopRow +
2)).NumberFormat = "@"
.Range("C" & CStr(TopRow + 3) & ":J" & CStr(TopRow +
3)).NumberFormat = "0%"
For intLoop = 0 To 3
.Range("C" & CStr(TopRow + intLoop) & ":D" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("E" & CStr(TopRow + intLoop) & ":F" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("G" & CStr(TopRow + intLoop) & ":H" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("I" & CStr(TopRow + intLoop) & ":J" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
Next intLoop
.Range("A" & CStr(TopRow + 3) & ":B" & CStr(TopRow +
3)).HorizontalAlignment = xlCenterAcrossSelection
.Cells(TopRow, 3).FormulaR1C1 = "Number Waiting"
.Cells(TopRow, 5).FormulaR1C1 = "12 Weeks"
.Cells(TopRow, 7).FormulaR1C1 = "18 Weeks"
.Cells(TopRow, 9).FormulaR1C1 = "22+ Weeks"
.Cells(TopRow + 1, 1).FormulaR1C1 = .Name
.Cells(TopRow + 2, 1).FormulaR1C1 = "Grand Total (32 services)"
.Cells(TopRow + 3, 1).FormulaR1C1 = "%"
.Cells(TopRow + 3, 3).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 5).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 7).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 9).FormulaR1C1 = strFormula
End With

End Sub


OK, I run the app, and then open the workbook it just built. I pick one of
the worksheets, scroll down to where TopRow happened to be for that sheet,
and I find that although the 4 text strings written to cells in TopRow are
correctly centered (centred here in UK!), the other ranges I specified as
xlCenterAcrossSelection are not. If I put the cursor in cell (TopRow + 1,
3), and begin to type something, it appears centered across the range of
cells between that cell and (TopRow + 1, 10). In fact, I can do the same in
any cell on that row, up to column 10, and the selection seems to be between
the chosen cell and the cell in column 10. Actually, I don't need any
formatting for the cells in even-numbered columns, because they'll never be
visible. So why has the code formatted them? (I can hear you saying 'What
if the user selects one of those cells and enters something in it?'. He
won't. A later step in the same application comes along and populates
everything.)

I get the same behaviour in cells on row (TopRow + 2), but oddly, those on
row (TopRow + 3) work as I intended. So I've tried making sure all the cells
on TopRow + 1 and TopRow + 2 have the same format as TopRow + 3 before
setting the horizontal alignment, then putting them back to how I need them
afterwards. No joy. I also tried putting something into the offending cells
with Formular1c1 beforehand, then removing it afterwards. Nope, that didn't
work either. What's going on here?

Thanks for any assistance
Andy

Can't get data centred across pairs of cells...?
 

Thanks Barb, that's solved the problem. It also simplifies the code for
putting borders round the cells.

:-)

Andy

"Barb Reinhardt" wrote:

I can't find any help for XLCenterAcrossSelection, so I tried it this way

With .Range("C" & CStr(TopRow + intLoop) & ":D" & _
CStr(TopRow + intLoop))
.Merge
.HorizontalAlignment = xlCenter
End With

and it seemed to work.

"Andy" wrote:

Hi folks.

Apologies for double-posting. I've asked this on the vb.generaldiscussion
group as well, since my app resides in VB6. Maybe Excel gurus will know a
little more about the behaviour I'm seeing.

I'm using VB with a reference to the Excel object model, so that my VB app
can build Excel workbooks 'on-the-fly'.

I have a sub for building a table. Code follows. I hope it's clear what I
intend it to do. (I've cut away some of the calls for simplicity - you don't
need to see the stuff for changing fonts and applying borders.)

Private Sub BuildP7Table(ByVal Sheet As Excel.Worksheet, ByVal TopRow As
Integer)
Dim strFormula As String
Dim intLoop As Integer

strFormula = "=IF(R[-2]C="""","""",IF(R[-1]C=""0"",""0%"",R[-2]C/R[-1]C))"

With Sheet
.Range("C" & CStr(TopRow + 1) & ":J" & CStr(TopRow +
2)).NumberFormat = "@"
.Range("C" & CStr(TopRow + 3) & ":J" & CStr(TopRow +
3)).NumberFormat = "0%"
For intLoop = 0 To 3
.Range("C" & CStr(TopRow + intLoop) & ":D" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("E" & CStr(TopRow + intLoop) & ":F" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("G" & CStr(TopRow + intLoop) & ":H" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
.Range("I" & CStr(TopRow + intLoop) & ":J" & CStr(TopRow +
intLoop)).HorizontalAlignment = xlCenterAcrossSelection
Next intLoop
.Range("A" & CStr(TopRow + 3) & ":B" & CStr(TopRow +
3)).HorizontalAlignment = xlCenterAcrossSelection
.Cells(TopRow, 3).FormulaR1C1 = "Number Waiting"
.Cells(TopRow, 5).FormulaR1C1 = "12 Weeks"
.Cells(TopRow, 7).FormulaR1C1 = "18 Weeks"
.Cells(TopRow, 9).FormulaR1C1 = "22+ Weeks"
.Cells(TopRow + 1, 1).FormulaR1C1 = .Name
.Cells(TopRow + 2, 1).FormulaR1C1 = "Grand Total (32 services)"
.Cells(TopRow + 3, 1).FormulaR1C1 = "%"
.Cells(TopRow + 3, 3).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 5).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 7).FormulaR1C1 = strFormula
.Cells(TopRow + 3, 9).FormulaR1C1 = strFormula
End With

End Sub


OK, I run the app, and then open the workbook it just built. I pick one of
the worksheets, scroll down to where TopRow happened to be for that sheet,
and I find that although the 4 text strings written to cells in TopRow are
correctly centered (centred here in UK!), the other ranges I specified as
xlCenterAcrossSelection are not. If I put the cursor in cell (TopRow + 1,
3), and begin to type something, it appears centered across the range of
cells between that cell and (TopRow + 1, 10). In fact, I can do the same in
any cell on that row, up to column 10, and the selection seems to be between
the chosen cell and the cell in column 10. Actually, I don't need any
formatting for the cells in even-numbered columns, because they'll never be
visible. So why has the code formatted them? (I can hear you saying 'What
if the user selects one of those cells and enters something in it?'. He
won't. A later step in the same application comes along and populates
everything.)

I get the same behaviour in cells on row (TopRow + 2), but oddly, those on
row (TopRow + 3) work as I intended. So I've tried making sure all the cells
on TopRow + 1 and TopRow + 2 have the same format as TopRow + 3 before
setting the horizontal alignment, then putting them back to how I need them
afterwards. No joy. I also tried putting something into the offending cells
with Formular1c1 beforehand, then removing it afterwards. Nope, that didn't
work either. What's going on here?

Thanks for any assistance
Andy