VBA子脚本超出范围-错误9故障

Question

有人能帮我做这段代码吗，我得到了一个下标的范围错误：

​

​

Dim ultimafilaauxiliarZB1 As Long
Dim ultimafilaauxiliarZB2 As Long
Dim ultimafilaauxiliarZN1 As Long
Dim ultimafilaauxiliarZN2 As Long

Dim cont As Integer

     If (Range("D23").Value > 49) Then

        ultimafilaauxiliarZB1 = Sheets("Hoja1").Range("L" & Rows.Count).End(xlUp).Row   ## HERE ERROR ##
        Sheets("Hoja1").Cells(ultimafilaauxiliarZB1 + 1, 12) = Range("D23").Value
        Sheets("Hoja1").Cells(ultimafilaauxiliarZB1 + 1, 13) = Range("C23").Value
        
     End If

Answer 1

您需要在哪个工作簿Sheets("Hoja1")中指定如下：

Workbooks("DASHBOARD_FINAL0000.xlsx").Sheets("Hoja1") 

或

ThisWorkbook.Sheets("Hoja1") 

请注意，ThisWorkbook是代码编写的工作簿，而ActiveWorkbook是有焦点的工作簿(在顶部)。

此外，您还需要为all (您的Range对象)指定一个工作簿和工作表。

 Dim wsHoja1 As Worksheet 'define your worksheet
 Set wsHoja1 = ThisWorkbook.Worksheets("Hoja1")

 If (ActiveSheet.Range("D23").Value > 49) Then
    ultimafilaauxiliarZB1 = wsHoja1.Range("L" & wsHoja1.Rows.Count).End(xlUp).Row
    wsHoja1.Cells(ultimafilaauxiliarZB1 + 1, 12) = ActiveSheet.Range("D23").Value
    wsHoja1.Cells(ultimafilaauxiliarZB1 + 1, 13) = ActiveSheet.Range("C23").Value
 End If