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

A

Andy

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
 
B

Barb Reinhardt

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 said:
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
Click to expand...
 
A

Andy

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

:)

Andy

Barb Reinhardt said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Multiple Areas from One Workbook to Another 4
advanced filter macro to locate values via multiple criteria 1
Need values not formulas - CopyMultipleSelection 2
VBA Sumproduct Help 6
Procedure Too Large problems 0
Data range in Array worksheets 2
Help needed to consolidate variable ranges in excel vba 5
Using variables in worksheet.range 1

Top