Excel VBA排序 [英] Excel VBA Sorting

问题描述

我是Excel VBA的新手.这似乎很简单. 我需要对Access数据库中的电子表格进行排序.

I'm new to Excel VBA. This seems like it should be simple though. I need to sort a spreadsheet from a Access database.

这是我的代码.

当前，我收到1004错误. "myRange =范围(选择)"上的对象_Global的范围方法失败"

Currently I get a 1004 error. "Method of Range of Object _Global Failed" on "myRange = Range(Selection)"

非常感谢您的帮助.

Sub sortBacklog()

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim myWorkSheet As Worksheet
Dim myRange As Range

Set appExcel = CreateObject("Excel.Application")
Set myWorkbook = appExcel.Workbooks.Open("C:\Users\gephilli\Desktop\Dispatch\SAP_Backlog.xls")
Set myWorkSheet = myWorkbook.Sheets(1)

myWorkSheet.Activate
myWorkSheet.Select

Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
myRange = Range(Selection)

myWorkSheet.Sort.SortFields.Clear

ActiveWorkbook.Worksheets("PLS Depot Backlog Report").Sort.SortFields.Add Key _
    :=Range("F2:F20491"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("PLS Depot Backlog Report").Sort
    .SetRange Range(myRange)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Save
ActiveWorkbook.Close
appExcel.Quit
Set myWorkSheet = Nothing
Set myWorkbook = Nothing
Set appExcel = Nothing
End Sub

推荐答案

通常无需在VBA中选择任何内容.那是录制的宏所做的，但这不是有效的方法.尝试: 设置myrange = Sheet1.Range("B1"，Sheet1.Range("B1").End(xlDown).End(xlToRight))

It is normally unnecessary to select anything in VBA. That is what recorded macros do, but it's not the efficient way. Try: Set myrange = Sheet1.Range("B1", Sheet1.Range("B1").End(xlDown).End(xlToRight))

您看到的问题实际上可能是发生错误的行中缺少设置".如果没有设置"，Excel会尝试处理范围内容，而使用设置"时，它将对范围对象本身起作用.

The problem you are seeing might actually be the lack of a "set" in the line where the error occurs. Without a "set" excel tries to work on range contents, with "set" it works on the range objects themselves.

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