Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Discrepancy between manually formatting sheet vs. using VBA macro
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 a - 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, -- Norm To reply, change domain to an adult feline. |
#2
Posted to microsoft.public.excel.programming, microsoft.public.excel
|
|||
|
|||
Discrepancy between manually formatting sheet vs. using VBA macro
Could you post the Macro?
Carlo On Dec 21, 12:30*pm, Norman Bullen 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 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 a * - 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, -- Norm To reply, change domain to an adult feline. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Discrepancy between manually formatting sheet vs. using VBA macro
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 Norman Bullen 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 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 a - 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, -- Norm To reply, change domain to an adult feline. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming, microsoft.public.excel
|
|||
|
|||
Discrepancy between manually formatting sheet vs. using VBA macro
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 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 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 a * - 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, -- Norm To reply, change domain to an adult feline. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Discrepancy between manually formatting sheet vs. using VBA macro
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 Dave Peterson wrote: 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 Norman Bullen 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 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 a - 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, -- Norm To reply, change domain to an adult feline. \ |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Discrepancy between manually formatting sheet vs. using VBA macro
So it sounds like you edited the code <vbg!
Norman Bullen wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
manually selected Excel cell formatting | Excel Discussion (Misc queries) | |||
Creating a template that is manually updated from another sheet. | Excel Worksheet Functions | |||
Auto filter run from a macro will not do the same as when done manually. | Excel Discussion (Misc queries) | |||
Single Sheet - Calculate Manually | Excel Programming | |||
Single Sheet - Calculate Manually | Excel Programming |