vba - 在 VBA 中获取最后一个单元格的问题

Question

我正在尝试编写代码来创建数据透视表(作为更大代码的一部分)，但我无法让它检测源数据。不幸的是，每次代码运行时，数据的行数都会不同，所以我希望代码自动找到最后一个包含数据的单元格。
我不断收到错误消息，指出需要对象或 undefined variable 。尽管去了 VBA 错误指南，但我找不到问题所在。

谁能看到这里的问题是什么？或者即使有更有效的方法来做到这一点？

Sub CreatePivotTable()
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As Range, LRow As Range, LCol As Range

'Determine the data range you want to pivot
 Set LRow = Cells(Rows.Count, 1).End(xlUp).Row
 Set LCol = Cells(1, Columns.Count).End(xlToLeft).Column
 Set SrcData = Worksheets("Raw Data").Range("A1:Cells(LRow,LCol)")

'Create a new worksheet
    Sheets.Add.Name = "Pivot Table"
'Where do you want Pivot Table to start?
    StartPvt = Worksheets("Occupancy").Range("A15")

'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,  SourceData:=SrcData)

'Create Pivot table from Pivot Cache
    Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="Occupancy")

'Create the headings and row and column orientation
    pvt.PivotFields("Precinct").Orientation = xlPageField
    pvt.PivotFields("Number").Orientation.xlDataField.Function = xlCount
    pvt.PivotFields("Captured Date").Orientation.xlColumnField.Position = 1
    pvt.PivotFields("Captured Session").Orientation.xlColumnField.Position = 2
    pvt.PivotFields("Session Location").Orientation.xlRowField.Position = 1

'Turn on Automatic updates/calculations --like screenupdating to speed up code
    pvt.ManualUpdate = False

End Sub

Answer 1

引号内的任何内容都是字符串，因此 "A1:Cells(LRow,LCol)"变成一个字符串并且不获取单元格的地址(LRow，LCol)

改变

Set SrcData = Worksheets("Raw Data").Range("A1:Cells(LRow,LCol)")

至

LastCol = Split(Cells(, LCol).Address, "$")(1)
Set SrcData = Worksheets("Raw Data").Range("A1:" & LastCol & LRow)

或对此

Set SrcData = Worksheets("Raw Data").Range("A1:" & Cells(LRow,LCol).Address(False, False))

也改变

Dim LRow As Range, LCol As Range

Set LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set LCol = Cells(1, Columns.Count).End(xlToLeft).Column

至

Dim LRow As Long, LCol As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
LCol = Cells(1, Columns.Count).End(xlToLeft).Column