Discrepancy between manually formatting sheet vs. using VBA macro

[Norman Bullen]

I receive a number of tab delimited text files which I need to convert
to Excel spreadsheets. Each file is similar in format: the first line
contains only one entry which is to become the "title" of the report.
The second line contains column headings (always the same) and the
remaining lines contain the variable data; every column in every row
always has a value.

When I format the spreadsheet manually I select A1:M1, Format Cells, and
merge cells, bold, and center horizontally. Then I select A2:M2, Format
Cells, and bold. Then I select column A, Format Cells, bold and custom
numeric format "0000". This produces exactly what I want.

I recorded exactly these operations into a VBA macro. When I run the
macro I see that bold and the custom numeric format are applied to all
columns from A to M.

I guessed that this happens because columns A through M are merged in
row 1 and, sure enough, if I edit the VBA macro so that the operation on
row 1 happens last, it produces the desired result.

My questions are:
- why does the VBA macro yield a different result than the commands
that were recorded?
- and is there any way around this, short of editing the the VBA macro?

Thanks,

 

[carlo]

Could you post the Macro?

Carlo

 

[Dave Peterson]

It seems like each version of excel treats merged cells differently. You'll
want to include the version of excel that you're using to get specific help.

Maybe you can change things around when you record a new macro. (I guess this
would be different from editing the existing macro????)

Format column A as 00000 and bold
then format A1:M2 as General (title and headers) (and bold???)
then merge A1:M1

 

[Pete_UK]

Well, you seem to have found a way around it, anyway, by changing the
order in the macro that you have!!

Pete

 

[Norman Bullen]

I have Excel 2003 (11.8146.8132) SP2

Here's the VBA macro. I invoke setupRGreport from the Run Macro menu. It
calls fixArray which was broken out into a separate routine because
several macros use it. This is the FIXED version; originally fixArray
was called _after_ the lines that setup row 1.

Norm

Sub fixArray()
'
' fixArray Macro
' Macro recorded 12/18/2007 by Norman Bullen
'

'
Columns("A:A").ColumnWidth = 5.57
Columns("A:A").Select
With Selection
.NumberFormat = "0000"
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub

Sub setupRGreport()
'
' setUpRGreport Macro
' Macro recorded 11/30/2007 by Norman Bullen
'
Call fixArray
Rows("1:1").RowHeight = 38.25
Range("A1:M1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
.MergeCells = True
.Font.Bold = True
End With
Range("A2:M2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
End With
Columns("B:B").ColumnWidth = 5.57
Columns("C:C").ColumnWidth = 7.29
Columns("D").ColumnWidth = 7.29
Columns("E:E").ColumnWidth = 9.86
Columns("E:E").ColumnWidth = 12.57
Columns("E:E").ColumnWidth = 11.71
Columns("G:G").ColumnWidth = 10.86
Columns("H:H").ColumnWidth = 5.71
Columns("H:H").ColumnWidth = 5
Columns("I:I").ColumnWidth = 10.43
Columns("J:J").ColumnWidth = 10.43
Columns("J:J").ColumnWidth = 10.86
Columns("L:L").ColumnWidth = 5.14
Columns("M:M").ColumnWidth = 26.57
Columns("K:K").ColumnWidth = 13.43
Range("A1:M1").Select

Call PageSetup

End Sub

It seems like each version of excel treats merged cells differently. You'll
want to include the version of excel that you're using to get specific help.

Maybe you can change things around when you record a new macro. (I guess this
would be different from editing the existing macro????)

Format column A as 00000 and bold
then format A1:M2 as General (title and headers) (and bold???)
then merge A1:M1

I receive a number of tab delimited text files which I need to convert
to Excel spreadsheets. Each file is similar in format: the first line
contains only one entry which is to become the "title" of the report.
The second line contains column headings (always the same) and the
remaining lines contain the variable data; every column in every row
always has a value.

When I format the spreadsheet manually I select A1:M1, Format Cells, and
merge cells, bold, and center horizontally. Then I select A2:M2, Format
Cells, and bold. Then I select column A, Format Cells, bold and custom
numeric format "0000". This produces exactly what I want.

I recorded exactly these operations into a VBA macro. When I run the
macro I see that bold and the custom numeric format are applied to all
columns from A to M.

I guessed that this happens because columns A through M are merged in
row 1 and, sure enough, if I edit the VBA macro so that the operation on
row 1 happens last, it produces the desired result.

My questions are:
- why does the VBA macro yield a different result than the commands
that were recorded?
- and is there any way around this, short of editing the the VBA macro?

Thanks,

\

 

[Dave Peterson]

So it sounds like you edited the code <vbg>!