找出运行时错误9时出错

Question

Sub summarize()
Dim sh As Worksheet
'Using sh for explicit summary worksheet for simplicity, doesn't have to reslect constantly
Dim summarySheet As Worksheet
'Defines the summarysheet as a worksheet
Dim summaryExists As Boolean
'Checks for truthiness


Dim summarysheet9 As Worksheet

Dim SummaryExists9 As Boolean



Dim n As Integer
n = ActiveWorkbook.Sheets.Count
'How many sheets are in the workbook?

Dim procMe()
ReDim procMe(n)
'Defining how many sheets we'll actually use

Dim ii As Integer

reportDate = WorksheetFunction.EoMonth(Date, -1)
'We should be doing month closings in the next month. This finds the last day of the prior month, when month closings are booked

RefDescription = Format(reportDate, "MMMM") & " close"
'The batch description entry

Dim headers() As Variant
headers = Array("Reference", "Reference Desc", "Type", "Reversal Date", _
  "Close Date", "Project", "Job#", "Cost Code", "Account", _
  "Variance Code", "Amount", "Transaction Date", "Detail Description")
'The headers for the summary sheet

ii = 0

REFCOL = 1
DESCCOL = 2
TYPECOL = 3
REVCOL = 4
CLOSECOL = 5
PROJCOL = 6
JOBCOL = 7
CCCOL = 8
ACCCOL = 9
VARCOL = 10
AMTCOL = 11
DATECOL = 12
DDESCCOL = 13

'Defines the location of each column, and what goes in each column

summaryExists = False
'Setting it to false initially, so that if it isn't found, we call it false. Just incase VBA decides to call it true by default.

SummaryExists9 = False


For Each sh In ActiveWorkbook.Sheets

  If sh.Name = "JournalEntryTransactions" Then
    summaryExists = True
    'If we find a sheet called JournalEntryTransactions, then we're good to go.
  End If


  If sh.Name = "JournalEntryTranscations9" Then
  SummaryExists9 = True
  'Same as above, but for 900
  End If


  If sh.Name = "Payroll" Then
    sh.Activate
    ii = ii + 1
    procMe(ii) = "Payroll"
  End If
  'If we find a payroll sheet, we like it

  If Val(sh.Name) > 0 Then
    ii = ii + 1
    procMe(ii) = sh.Name
    'If the name of the sheet is an integer, we like it

  Else
    'Debug.Print "I don't know what to do with " & sh.Name
    'Debugging when it wasn't working too well

  End If

Next sh

ReDim Preserve procMe(ii)

If Not summaryExists Then
  Set summarySheet = ActiveWorkbook.Sheets.Add
  summarySheet.Name = "JournalEntryTransactions"
  'If we don't have a summary sheet, add it in

Else
  Set summarySheet = ActiveWorkbook.Sheets("JournalEntryTransactions")
  summarySheet.Range("A1", summarySheet.Cells.SpecialCells(xlCellTypeLastCell)).Clear
End If
'If we already have a summary sheet, we're clearing it out

If Not SummaryExists9 Then
  Set summarysheet9 = ActiveWorkbook.Sheets.Add
  summarysheet9.Name = "JournalEntryTransactions9"
  'If we don't have a summary sheet, add it in

Else
  **Set summarysheet9 = ActiveWorkbook.Sheets("JournalEntryTransactions9")**
  summarySheet.Range("A1", summarySheet.Cells.SpecialCells(xlCellTypeLastCell)).Clear
End If
'If we already have a summary sheet, we're clearing it out

接近尾声时，ActiveWorkbook.Sheets("JournalEntryTransactions9") = Set summarysheet9导致了一个运行时错误9。对于我来说，我无法找到我所缺少的东西。

我想要做的是，如果我们已经生成了一个摘要表(9)，它就会清除表(9)中的所有内容。

“常规”工作表工作(上面的代码部分)-只是工作表9不工作。当我没有床单的时候，它会产生很好的效果，它只是因为清理它而生气。

Answer 1

试试下面的代码

Sub summarize()

Dim sh As Worksheet
'Using sh for explicit summary worksheet for simplicity, doesn't have to reslect constantly
Dim summarySheet As Worksheet
'Defines the summarysheet as a worksheet
Dim summaryExists As Boolean
'Checks for truthiness


Dim summarysheet9 As Worksheet

Dim SummaryExists9 As Boolean



Dim n As Integer
n = ActiveWorkbook.Sheets.Count
'How many sheets are in the workbook?

Dim procMe()
ReDim procMe(n)
'Defining how many sheets we'll actually use

Dim ii As Integer

reportDate = WorksheetFunction.EoMonth(Date, -1)
'We should be doing month closings in the next month. This finds the last day of the prior month, when month closings are booked

RefDescription = Format(reportDate, "MMMM") & "close"
'The batch description entry

Dim headers() As Variant
headers = Array("Reference", "Reference Desc", "Type", "Reversal Date", _
  "Close Date", "Project", "Job#", "Cost Code", "Account", _
  "Variance Code", "Amount", "Transaction Date", "Detail Description")
'The headers for the summary sheet

ii = 0

REFCOL = 1
DESCCOL = 2
TYPECOL = 3
REVCOL = 4
CLOSECOL = 5
PROJCOL = 6
JOBCOL = 7
CCCOL = 8
ACCCOL = 9
VARCOL = 10
AMTCOL = 11
DATECOL = 12
DDESCCOL = 13

'Defines the location of each column, and what goes in each column

summaryExists = False
'Setting it to false initially, so that if it isn't found, we call it false. Just incase VBA decides to call it true by default.

SummaryExists9 = False


For Each sh In ActiveWorkbook.Sheets

  If sh.Name = "JournalEntryTransactions" Then
    summaryExists = True
    'If we find a sheet called JournalEntryTransactions, then we're good to go.
  End If


  If sh.Name = "JournalEntryTransacations9" Then
  SummaryExists9 = True
  'Same as above, but for 900
  End If


  If sh.Name = "Payroll" Then
    sh.Activate
    ii = ii + 1
    procMe(ii) = "Payroll"
  End If
  'If we find a payroll sheet, we like it

  If Val(sh.Name) > 0 Then
    ii = ii + 1
    procMe(ii) = sh.Name
    'If the name of the sheet is an integer, we like it

  Else
    'Debug.Print "I don't know what to do with " & sh.Name
    'Debugging when it wasn't working too well

  End If

Next sh

ReDim Preserve procMe(ii)

If Not summaryExists Then
  Set summarySheet = ActiveWorkbook.Sheets.Add
  summarySheet.Name = "JournalEntryTransactions"
  'If we don't have a summary sheet, add it in

Else
  Set summarySheet = ActiveWorkbook.Sheets("JournalEntryTransactions")
  summarySheet.Range("A1", summarySheet.Cells.SpecialCells(xlCellTypeLastCell)).Clear
End If
'If we already have a summary sheet, we're clearing it out

If Not SummaryExists9 Then
  Set summarysheet9 = ActiveWorkbook.Sheets.Add
  summarysheet9.Name = "JournalEntryTransactions9"
  'If we don't have a summary sheet, add it in

Else
  Set summarysheet9 = ActiveWorkbook.Sheets("JournalEntryTransacations9")
  summarySheet.Range("A1", summarySheet.Cells.SpecialCells(xlCellTypeLastCell)).Clear
End If
'If we already have a summary sheet, we're clearing it out

End Sub