帮助Excel宏中的VBA语法

本文介绍了帮助Excel宏中的VBA语法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好。

我正在尝试修改Excel（2007）宏，以便正确对数据列执行排序。

我录制了一个宏以便开始编码。代码只是根据指定的排序条件对数据列进行排序。以下是录制的宏代码：

I recorded a macro in order to get the coding started. The code simply sorts data columns according to specified sort criteria. Here is the recorded macro code:

Sub Table_Sort_Print（）

'

'Table_Sort_Print Macro

'用于标签打印的分类表

'

'键盘快捷键：Ctrl + p

'

   ;&NBSP; ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 5

   列（"A：I"）。选择

   范围（"I1"）。激活

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Clear

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Add Key：= Range（" A2：A16"）_

        ，SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Add Key：= Range（" B2：B16"）_

        ，SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Add Key：= Range（" C2：C16"）_

        ，SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Add Key：= Range（" D2：D16"）_

        ，SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Add Key：= Range（" E2：E16"）_

        ，SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Add Key：= Range（" F2：F16"）_

        ，SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Add Key：= Range（" G2：G16"）_

        ，SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

    ActiveWorkbook.Worksheets（" Sheet1"）。Sort.SortFields.Add Key：= Range（" I2：I16"）_

        ，SortOn：= xlSortOnValues，订单：= xlAscending，DataOption：= xlSortNormal

   使用ActiveWorkbook.Worksheets（"Sheet1"）。排序

        .SetRange范围（"A1：I16"）

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        。应用

   结束与$
    ActiveWindow.ScrollColumn = 6

    ActiveWindow.ScrollColumn = 5

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 2

   范围（"B2"）。选择

结束子

Sub Table_Sort_Print()
'
' Table_Sort_Print Macro
' Sort table for label printing
'
' Keyboard Shortcut: Ctrl+p
'
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    Columns("A:I").Select
    Range("I1").Activate
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2:F16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G2:G16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I2:I16") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:I16")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    Range("B2").Select
End Sub

此代码工作正常，但排序仅限于指定的范围。例如，参见上面代码中的Range（"A2：A16"）。我想要做的是为可能存在的整个数据范围排序指定的列。为了实现这个目标，I
必须将列范围定义为整个连续数据集。

This code works fine, except that the sorting is limited to the ranges specified. For example, see Range("A2:A16") in above code. What I want to do is to sort the specified columns for the ENTIRE RANGE OF DATA WHICH MAY EXIST. To accomplish this goal, I must define the columnar ranges to be the entire sets of contiguous data.

在搜索用于修改上述宏的适当代码时，我来了跨越以下内容：

In searching for appropriate code with which to modify the above macro, I came across the following:

ActiveSheet.Range（" a1"，ActiveSheet.Range（" a1"）。End（xlDown））。选择

ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select

该代码据称选择了可能存在于指定列中的所有连续数据（在上面的例子中，"A"列，从单元格A1开始）。我试图做的是用上面的代码替换定义的范围（例如：范围（"A2：A16"））。不幸的是，
无论我尝试过什么，我都遇到了VBA语法错误。

This code allegedly selects all contiguous data which may exist in the specified column (in the above case, the "A" column, starting with cell A1). What I attempted to do is to replace the defined ranges (eg: Range("A2:A16")) with the above code. Unfortunately, I have run into VBA syntax errors no matter what I have tried.

有人请指教我如何使用正确的语法修改我的宏代码？

Will someone please give me direction on how to modify my macro code using proper syntax?

非常感谢。

Mark Shneour

Mark Shneour
[email protected]

i16

帮助Excel宏中的VBA语法

问题描述

推荐答案