VBA中动态Range排序代码无法执行的问题求助

将第一个工作簿中的RLDSht工作表复制到第二个工作簿并命名为USSht后，尝试对USSht执行排序操作，即使激活工作表，代码仍无法执行。仅使用精确固定范围时排序才生效，询问Range(Cells(fR, 1), Cells(fR, lC))写法存在什么问题。

原始代码 #

Public WorkbookName As String
Public WorkbookVV As Workbook
Public RLDSht As Worksheet
Public USSub As Worksheet
Public NoGrey As Worksheet
Public ws As Worksheet

Sub SelectWorkbook()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False

WorkbookName = Application.GetOpenFilename("Excel files (*.xlsm), *xlsm", 1, "Select your workbook", , False)
If WorkbookName <> "False" Then
    Set WorkbookVV = Workbooks.Open(WorkbookName)
    
    For Each ws In WorkbookVV.Sheets
        If Not ws.Cells.Find("Data type") Is Nothing Then
            RLDShtExist = True
            Set RLDSht = ws
            Exit For
        End If
    Next ws
    
    If RLDShtExist = False Then
        MsgBox "Erreur: Le workbook sélectionné ne contient pas d'onglet Regulatory Line Data"
        WorkbookName = ""
        Exit Sub
    End If
Else
    Exit Sub
End If

If RLDSht.FilterMode Then RLDSht.ShowAllData

RLDSht.Copy after:=Workbooks("US Submission table.xlsm").Worksheets("US Submission Table")
Set Ussht = ActiveSheet

With Ussht
    If .FilterMode Then .ShowAllData
    lR = .Cells(Rows.Count, 1).End(xlUp).Row
    'last column
    lC = .Cells(lR, Columns.Count).End(xlToLeft).Column
    'first row
    fR = .Cells(lR, 1).End(xlUp).Row
    
    Set cdt = Range(.Cells(fR, 1), .Cells(fR, lC)).Find("Data type")
    If Not cdt Is Nothing Then
        c = cdt.Column
    Else
        MsgBox "La colonne Data type n'est pas présenté dans ce tab RLD"
    End If
End With
Ussht.Activate
Ussht.Range(Cells(fR, 1), Cells(fR, lC)).Sort Key1:=Range("A12"), Order1:=xlDescending
End Sub

尝试过的无效写法 #

Ussht.Range(Cells(fR, 1), Cells(fR, lC)).Sort Key1:=Range(Cells(fR, 1), Cells(fR, 1)), Order1:=xlDescending

唯一生效的写法 #

Ussht.Range("A12:AB1740").Sort Key1:=Range("A12"), Order1:=xlDescending, Header:=xlYes

核心问题：未明确指定Cells的父对象 #

Ussht.Range(Cells(fR,1), Cells(fR,lC))中的Cells没有绑定到Ussht工作表，默认会引用当前激活的工作表的单元格。即使执行了Ussht.Activate，若代码执行过程中上下文切换（比如复制工作表后的激活状态异常），Cells仍可能指向错误的工作表，导致Range引用失效。

此外，原代码还有两个关键缺陷：

1. 排序范围仅选中表头行（fR为表头行），未包含数据区域，自然看不到排序效果。
2. 未指定Header参数，Excel无法识别表头，会将表头当作数据参与排序。

修正后的代码 #

将所有Cells、Range通过.绑定到Ussht，同时修正排序范围和参数：

Public WorkbookName As String
Public WorkbookVV As Workbook
Public RLDSht As Worksheet
Public USSub As Worksheet
Public NoGrey As Worksheet
Public ws As Worksheet

Sub SelectWorkbook()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False

WorkbookName = Application.GetOpenFilename("Excel files (*.xlsm), *xlsm", 1, "Select your workbook", , False)
If WorkbookName <> "False" Then
    Set WorkbookVV = Workbooks.Open(WorkbookName)
    
    Dim RLDShtExist As Boolean ' 声明变量，避免隐式变体
    RLDShtExist = False
    For Each ws In WorkbookVV.Sheets
        If Not ws.Cells.Find("Data type") Is Nothing Then
            RLDShtExist = True
            Set RLDSht = ws
            Exit For
        End If
    Next ws
    
    If Not RLDShtExist Then
        MsgBox "Erreur: Le workbook sélectionné ne contient pas d'onglet Regulatory Line Data"
        WorkbookName = ""
        Exit Sub
    End If
Else
    Exit Sub
End If

If RLDSht.FilterMode Then RLDSht.ShowAllData

RLDSht.Copy after:=Workbooks("US Submission table.xlsm").Worksheets("US Submission Table")
Set Ussht = ActiveSheet

With Ussht
    If .FilterMode Then .ShowAllData
    Dim lR As Long, lC As Long, fR As Long, c As Long
    lR = .Cells(.Rows.Count, 1).End(xlUp).Row
    lC = .Cells(lR, .Columns.Count).End(xlToLeft).Column
    fR = .Cells(lR, 1).End(xlUp).Row ' 假设fR是表头行
    
    Dim cdt As Range
    Set cdt = .Range(.Cells(fR, 1), .Cells(fR, lC)).Find("Data type")
    If Not cdt Is Nothing Then
        c = cdt.Column
    Else
        MsgBox "La colonne Data type n'est pas présenté dans ce tab RLD"
        Exit Sub ' 找不到列时退出，避免后续错误
    End If
    
    ' 排序：完整数据区域，按Data type列降序，指定表头
    .Range(.Cells(fR, 1), .Cells(lR, lC)).Sort _
        Key1:=.Cells(fR, c), _
        Order1:=xlDescending, _
        Header:=xlYes
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.AskToUpdateLinks = True
End Sub

关键修正点 #

• 所有Cells、Range前添加.，确保隶属于Ussht工作表，彻底避免上下文引用错误。
• 排序范围覆盖从表头行到最后一行的所有列，包含完整数据区域。
• 指定Header:=xlYes，让Excel自动跳过表头行排序。
• 用找到的c列（"Data type"列）作为排序键，替代固定单元格引用，提升代码灵活性。
• 补充变量声明，避免隐式变体类型，提升代码稳定性。
• 恢复Excel默认设置（DisplayAlerts等），避免影响后续操作。

内容的提问来源于stack exchange，提问作者Le Dieu Ha PHI