帮助Excel宏中的VBA语法 [英] Help with VBA Syntax in Excel Macro
问题描述
大家好。
我正在尝试修改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 @ dotmom .com
Mark Shneour
mark@dotmom.com
推荐答案
尝试在VBA中重新发布此问题论坛或等待版主在适当的论坛上为您提供更好的回复
http ：//social.msdn.microsoft.com/forums/en-US/isvvba/threads/
kaymaf
这篇关于帮助Excel宏中的VBA语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
