本文介绍了帮助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

'

   ;  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]

 

推荐答案

kaymaf


这篇关于帮助Excel宏中的VBA语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-23 22:14