vba - 运行时错误 '1004' 'Range' of object'_Global' 失败

Question

执行命令时出现此错误。

我已经尝试解决但仍然有同样的错误。知道如何解决这个问题。

一段代码 :

Dim col As Long

col = Range("type").Column 'this line produces an error

这是完整的代码:

Sub refresh()

Dim imin As Long, col As Long, imax As Long, column As Long, numRow As Long
Dim myLine As Integer

myLine = 2
numRow = Sheets("merge").Range("A" & Rows.Count).End(xlUp).Row

If numRow > myLine Then

    imin = Range("type").Row + 1
    col = Range("type").Column

    With Sheets("temp")
        .Cells.Delete
        If Sheets("merge").Range("A" & Rows.Count).End(xlUp).Row >= myLine Then
            Sheets("merge").Range("C" & myLine & ":E" & Sheets("merge").Range("E" & Rows.Count).End(xlUp).Row).Copy .Range("A1")
        End If

        Application.CutCopyMode = False

        For column = 1 To 3
            If .Range(Fct_LetCol(column) & Rows.Count).End(xlUp).Row > 1 Then
                .Range(.Cells(1, column), .Cells(.Range(Fct_LetCol(column) & Rows.Count).End(xlUp).Row, col)).RemoveDuplicates Columns:=1, header:=xlNo
                imax = .Range(Fct_LetCol(column) & Rows.Count).End(xlUp).Row
                tridata Sheets("temp"), Range(Fct_LetCol(column) & "1:" & Fct_LetCol(column) & imax), Range(Fct_LetCol(column) & "1:" & Fct_LetCol(column) & imax), xlAscending, xlSortNormal, xlGuess
            End If
        Next column
    End With

    With Sheets("selection")
        imax = .UsedRange.Rows.Count + .UsedRange.Row - 1
        If imax >= imin Then .Rows(imin & ":" & imax).Delete
        Sheets("temp").Range("A1:A" & Sheets("temp").Range("A" & Rows.Count).End(xlUp).Row).Copy .Cells(imin, col)
        Sheets("temp").Range("B1:B" & Sheets("temp").Range("B" & Rows.Count).End(xlUp).Row).Copy .Cells(imin, col + 2)
        Sheets("temp").Range("C1:C" & Sheets("temp").Range("C" & Rows.Count).End(xlUp).Row).Copy .Cells(imin, col + 4)
        Application.CutCopyMode = False
        imax = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With

    Sheets("temp").Cells.Delete
    Sheets("selection").Activate
    Application.Calculation = xlAutomatic

Else
    msg = MsgBox("Error", 64, "Error")
End If   

End Sub

Answer 1

运行此修改后的代码后:

 Sub refresh()

Dim imin As Long, col As Long, imax As Long, numRow As Long
Dim myLine As Integer

myLine = 2
numRow = Sheets("merge").range("A" & Rows.Count).End(xlUp).Row

If numRow > myLine Then

imin = range("type").Row + 1
col = range("type").Column

With Sheets("selection")
    imax = .UsedRange.Rows.Count + .UsedRange.Row - 1
    If imax >= imin Then .Rows(imin & ":" & imax).Delete
    Sheets("temp").range("A1:A" & Sheets("temp").range("A" & Rows.Count).End(xlUp).Row).Copy .Cells(imin, col)
    Sheets("temp").range("B1:B" & Sheets("temp").range("B" & Rows.Count).End(xlUp).Row).Copy .Cells(imin, col + 2)
    Sheets("temp").range("C1:C" & Sheets("temp").range("C" & Rows.Count).End(xlUp).Row).Copy .Cells(imin, col + 4)
    Application.CutCopyMode = False
    imax = .UsedRange.Rows.Count + .UsedRange.Row - 1
End With

Sheets("temp").Cells.Delete
Sheets("selection").Activate
Application.Calculation = xlAutomatic

Else
  msg = MsgBox("Error", 64, "Error")
End If

End Sub

我发现在运行该过程时，事件工作表与 "type" 范围不同的工作表不同。位于，导致 "type" 范围内的引用错误(检查您的名称管理器)。因此，请务必将事件工作表设置为 "type"在给 imin 赋值之前和 col , 或使用 imin = Sheets("RelevantSheetName").Range("type").Row + 1等等