excel - 运行以下 VBA 代码时如何删除错误 Method 'Range' of object '-Worksheet' failed

Question

我正在尝试在工作表中编写 vlookup 值，但表数组位于不同的工作表中。我在行中收到此运行时错误“设置 DataRange = Sourcesheet.Range(StartPoint, DownCel)”。

Option Explicit

Sub test()
Dim Filepath As Variant, SetRange As Range, DataRange As Range, StartPoint 
As Range
Dim LastCol As Long, DownCel As Long, NewRange As String
Dim Sourcesheet As Worksheet, wb2 As Workbook, wb As Workbook

Filepath = Application.GetOpenFilename
Set wb2 = Workbooks.Open(Filename:=Filepath)
Set Sourcesheet = wb2.Worksheets("Gold_Pending")

Set StartPoint = Sourcesheet.Range("F2")
DownCel = StartPoint.End(xlDown).Row
Set DataRange = Sourcesheet.Range(StartPoint, DownCel)

NewRange = wb2.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

With wb.Sheets("GoldPending")

Range("G2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-7],NewRange,1,0)"
Range("G2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
Range(Selection, Selection.End(xlDown)).Select

End With

End Sub

Answer 1

因为您没有“完整/完整”范围:

Range(StartPoint, DownCel)

分解各个部分:

Set StartPoint = Sourcesheet.Range("F2") 'This one will give you the starting point of your range i.e. both Column ("F") and Row ("2") location.
DownCel = StartPoint.End(xlDown).Row 'This will only give you "last" row (lets say 13).

因此您当前正在编写:

Sourcesheet.Range(StartPoint, DownCel) => Sourcesheet.Range("F2", 13).

更改为:

Sourcesheet.Range(StartPoint, DownCel) => Sourcesheet.Range(StartPoint, "F" & DownCel)

<小时/>

用于说明的示例代码:

Sub test()
Dim StartPoint As Range
Dim DataRange As Range

Set Sourcesheet = Worksheets("Sheet1")

Set StartPoint = Sourcesheet.Range("F2")
DownCel = StartPoint.End(xlDown).Row
Set DataRange = Sourcesheet.Range(StartPoint, "F" & DownCel)

DataRange.Select 'To visualize what Datarange will select

End Sub

<小时/>

使用“With”以两种方式进行编辑:

Sub test()
Dim StartPoint As Range
Dim DataRange As Range
Dim wb As Workbook 'Alternative 1 & 2
Dim sht As Worksheet 'Alternative 1
Dim ws As Worksheet


Set Sourcesheet = Worksheets("Sheet1")

Set wb = ActiveWorkbook 'Alternative 1 & 2
Set sht = wb.Worksheets("Sheet1") 'Alternative 1


Set StartPoint = Sourcesheet.Range("F2")
DownCel = StartPoint.End(xlDown).Row
Set DataRange = Sourcesheet.Range(StartPoint, "F" & DownCel)

DataRange.Select 'To visualize what Datarange will select



With sht 'Alternative 1
With wb.Worksheets("GoldPending") 'Alternative 2

'Do stuff...

End With
End With

End Sub