Discrepancy between manually formatting sheet vs. using VBA macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Norman Bullen - 21 Dec 2007 03:22 GMT

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,

Signature

Norm

To reply, change domain to an adult feline.

Reply to this Message

carlo - 21 Dec 2007 04:11 GMT

Could you post the Macro?

Carlo

On Dec 21, 12:30 pm, Norman Bullen <n...@BlackKittenAssociates.com>
wrote:

> 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
[quoted text clipped - 26 lines]
>
> To reply, change domain to an adult feline.

Reply to this Message

Dave Peterson - 21 Dec 2007 12:18 GMT

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

Signature

Dave Peterson

Reply to this Message

Norman Bullen - 22 Dec 2007 04:24 GMT

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: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.
[quoted text clipped - 36 lines]
>>
>>To reply, change domain to an adult feline.

Reply to this Message

Dave Peterson - 22 Dec 2007 12:04 GMT

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

> I have Excel 2003 (11.8146.8132) SP2
>
[quoted text clipped - 4 lines]
>
> Norm

Reply to this Message

Pete_UK - 21 Dec 2007 16:07 GMT

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

Pete

On Dec 21, 3:30 am, Norman Bullen <n...@BlackKittenAssociates.com>
wrote:

Reply to this Message