Find() 返回"对象变量或未设置块变量"

Question

这个代码块工作得很好，但是我删除了Find()上面的一些行，这些行破坏了它。什么好主意吗?

Sub CopySheet()
Dim TotalRow As Integer

Set NurselineBook = ThisWorkbook
TotalRow = Range("$C:$C").Find(What:="Grand Total", LookIn:=xlValues, LookAt:=xlWhole).Row
Range("A1:L" & TotalRow).Select
Range("Ah1").Activate
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

MsgBox "Dashboard Copied"
End Sub

Answer 1

Sub CopyTable()

Const wsName As String = "Sheet1"
Const ColumnsAddress As String = "A:L"
Const FirstRow As Long = 1
Const CriteriaColumn As Long = 3
Const gtString As String = "Grand Total"

Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)

Dim srg As Range
With ws.Columns(ColumnsAddress)
Set srg = .Resize(ws.Rows.Count - FirstRow + 1).Offset(FirstRow - 1)
End With

Dim gtcell As Range: Set gtcell = srg.Columns(CriteriaColumn) _
.Find(gtString, , xlValues, xlWhole, , xlPrevious)
If gtcell Is Nothing Then
MsgBox "Could not find '" & gtString & "'.", vbCritical
Exit Sub
End If

Dim drg As Range
Set drg = srg.Resize(gtcell.Row - FirstRow + 1)

drg.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

MsgBox "Dashboard Copied", vbInformation
End Sub