格式化宏不适用于所有工作表 [英] Formatting Macro doesnt work on all worksheets

问题描述

下面是我的代码中处理格式化生成的输出的一部分。所以，简单来说，我的宏从各种来源拉出数据，比较它们，并产生多个工作表中的市场差异和追赶等统计数据。我需要做的是将所有工作表中的百分比单元格格式化。发生的情况是，格式化仅适用于生成的第一个工作表，而不在随后的工作表中。格式化为逗号的代码部分在所有工作表上都可以正常工作，但不是百分比。
我真的不知道问题是什么。我试过调整，但没有成功。我真的很时间紧张，需要尽快工作，所以如果这个问题已经存在，我提前道歉。
PFB代码片段：

Below is part of my code that deals with formatting the output generated. So, to put it concisely, my macro pulls data from various sources, compares them and generates statistics like Market variance and catch-up etc. in multiple worksheets. What I need it to do is a format a range of cells as "Percentage" in all worksheets. What's happening is that the formatting only applies itself in the first worksheet generated and not in the subsequent worksheets. The part of code that formats into "comma" works fine on all sheets but not percentage. I'm not really sure what the problem is. I've tried tweaking but with no success. I'm really pressed for time and need this to work as soon as possible, so I apologize in advance if this question exists already. PFB code snippet:

ActiveWorkbook.Sheets.Select
Range(Cells(100, 2), Cells(142, MarketRangeColumn + 10)).Select
Selection.Style = "Comma"
Selection.numberformat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.numberformat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range(Cells(143, 2), Cells(146, MarketRangeColumn + 10)).Select
Selection.Style = "Percent"

提前感谢！

推荐答案

当您选择所有的表格时，代码无法确定您希望代码运行的工作表（尽管您的方法是逻辑的VBA只是不实际上几乎所有的编程语言都需要你指定要使用的对象），所以它使用第一个选择。

When you select all the sheets like that the code has no way to determine which sheet you want the code to run on (though your approach is logical VBA just doesn't work like that, in fact nearly all programming languages require you to specify the object you want to use), so it uses the first in the selection.

使用 for循环移动页面s。此外，您不需要选择一些操作，您可以使用使用块缩短代码：

Use a for loop to move through the sheets. Also, you don't need to select something to act on it and you can use a With block to shorten the code a bit:

For s = 1 To Sheets.Count

    With Sheets(s).Range(Cells(100, 2), Cells(142, MarketRangeColumn + 10))
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    Sheets(s).Range(Cells(143, 2), Cells(146, MarketRangeColumn + 10)).Style = "Percent"

Next s

这篇关于格式化宏不适用于所有工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！