VBA排序宏不工作 [英] VBA Sort Macro not working

查看：209
本文介绍了VBA排序宏不工作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

排序代码不再工作。它第一次工作然后我关闭它并打开它，然后它给我一个错误。 （我没有改变任何东西。）它给了我：

The sort code is not working anymore. It worked the first time. Then I closed it and opened it and then it gave me an error. (I didn't change anything.) It gave me:


错误438：对象不支持此属性或方法

Error 438: Object doesn't support this property or method

在此行上：

DataWB.DataSheet.Sort.SortFields.Add Key:=Range(FNOrdCol), SortOn:=xlSortOnValues, _
                                     Order:=xlAscending, DataOption:=xlSortNormal`

排序代码片段：

    'Alpahebtical order
    DataSheet.Range("A1").Select
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Find(What:=FNOrder, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    FNOrdCol = ActiveCell.Address
    DataWB.DataSheet.Sort.SortFields.Clear
    DataWB.DataSheet.Sort.SortFields.Add Key:=Range(FNOrdCol), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With DataWB.DataSheet.Sort
        .SetRange DataSheet.Cells
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

整个代码：

Sub iGetData()

Dim ValidatorWB As Workbook
Dim PopDetail As Worksheet
Dim DataSheetName As String
Dim DataWB As Workbook
Dim DataSheet As Worksheet
Dim Ret
Dim DWBName As String
Dim FNOrder As String
Dim FNOrdCol As String

Set PopDetail = Worksheets("PopulateWireframe")
Set ValidatorWB = Workbooks(ActiveWorkbook.Name)
DataSheetName = Range("F18").Value
FNOrder = Range("F33").Value

Application.ScreenUpdating = False

'Open data file
Ret = IsWorkBookOpen(PopDetail.Range("C18").Value)
If Ret = False Then

Workbooks.Open PopDetail.Range("C18").Value
DataFileName = ActiveWorkbook.Name
Set DataWB = Workbooks(DataFileName)
Set DataSheet = Worksheets(DataSheetName)

Dim FilterColumn As String
Dim FilterCriteria As String
Dim ColumnNumber As Integer

'Set filter
With DataSheet
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
End With

ValidatorWB.Activate
PopDetail.Activate

For x = 21 To 30

If Range("E" & x).Value <> "" And Range("F" & x).Value <> "" Then

    FilterColumn = PopDetail.Range("E" & x).Value
    FilterCriteria = PopDetail.Range("F" & x).Value

    DataWB.Activate
    DataSheet.Activate

    DataSheet.Range("A1").Select

    Selection.End(xlToLeft).Select

    ActiveCell.Rows("1:1").EntireRow.Select

    Selection.Find(What:=FilterColumn, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    ColumnNumber = ActiveCell.Column

    DataSheet.AutoFilterMode = False
    DataSheet.Range("A1").AutoFilter Field:=ColumnNumber, Criteria1:=FilterCriteria

End If

    ValidatorWB.Activate
    PopDetail.Activate

'x = x + 1

Next x

    DataWB.Activate
    DataSheet.Activate

    'Alpahebtical order
    DataSheet.Range("A1").Select
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Find(What:=FNOrder, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    FNOrdCol = ActiveCell.Address
    DataWB.DataSheet.Sort.SortFields.Clear
    DataWB.DataSheet.Sort.SortFields.Add Key:=Range(FNOrdCol), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With DataWB.DataSheet.Sort
        .SetRange DataSheet.Cells
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'Copy data
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    'Paste data to validator
    ValidatorWB.Activate
    ValidatorWB.Sheets.Add().Name = "ValidatorData"
    ActiveCell.Offset(3, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    ActiveCell.Columns("A:A").EntireColumn.ColumnWidth = 15
    Application.CutCopyMode = False

'DataWB.Close savechanges:=False
If DataWB.Windows(1).Visible = True Then
DataWB.Windows(1).Visible = False
End If

Application.ScreenUpdating = True

PopDetail.Activate

Else

DWBName = GetFilenameFromPath(PopDetail.Range("C18").Value)
Set DataWB = Workbooks(DWBName)
DataWB.Activate
Set DataSheet = Worksheets(DataSheetName)
DataSheet.Activate
With DataSheet
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
End With

ValidatorWB.Activate
PopDetail.Activate

For x = 21 To 30

If Range("E" & x).Value <> "" And Range("F" & x).Value <> "" Then

    FilterColumn = PopDetail.Range("E" & x).Value
    FilterCriteria = PopDetail.Range("F" & x).Value

    DataWB.Activate
    DataSheet.Activate

    DataSheet.Range("A1").Select

    Selection.End(xlToLeft).Select

    ActiveCell.Rows("1:1").EntireRow.Select

    Selection.Find(What:=FilterColumn, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    ColumnNumber = ActiveCell.Column

    DataSheet.AutoFilterMode = False
    DataSheet.Range("A1").AutoFilter Field:=ColumnNumber, Criteria1:=FilterCriteria

End If

    ValidatorWB.Activate
    PopDetail.Activate

'x = x + 1

Next x

    DataWB.Activate
    DataSheet.Activate

    'Alpahebtical order
    DataSheet.Range("A1").Select
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Find(What:=FNOrder, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    FNOrdCol = ActiveCell.Address
    'DataWB.DataSheet.Sort.SortFields.Clear
    DataWB.DataSheet.Sort.SortFields.Add Key:=Range(FNOrdCol), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With DataWB.DataSheet.Sort
        .SetRange DataSheet.Cells
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'Copy data
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    'Paste data to validator
    ValidatorWB.Activate
    ValidatorWB.Sheets.Add().Name = "ValidatorData"
    ActiveCell.Offset(3, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    ActiveCell.Columns("A:A").EntireColumn.ColumnWidth = 15
    Application.CutCopyMode = False

'DataWB.Close savechanges:=False
If DataWB.Windows(1).Visible = True Then
DataWB.Windows(1).Visible = False
End If

Application.ScreenUpdating = True

PopDetail.Activate

End If

End Sub


推荐答案

已修复。我更改了DataWB.DataSheet在所有引用只是ActiveSheet。感谢您的帮助。

Fixed. I changed DataWB.DataSheet in all references to just ActiveSheet. Thank you for the help.

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

查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆