运行时438 VBA

Question

我使用VBA时出现运行时438错误。你能帮我一下吗？我是初学者。

Private Sub CommandButton1_Click()    
Dim nwb As Workbook     
Set nwb = Workbooks.Open("G:\worksheet.xlsm")     

    CopyLastRow = nwb.Cells(nwb.Rows.Count, "A").End(x1Up).Row + 1

    Cells(CopyLastRow, 1).Value = TextBox1.Value     
    Cells(CopyLastRow, 2).Value = TextBox2.Value     
    Cells(CopyLastRow, 3).Value = ComboBox1.Value     
    Cells(CopyLastRow, 4).Value = TextBox3.Value     
    Cells(CopyLastRow, 5).Value = ComboBox2.Value    
    Cells(CopyLastRow, 6).Value = TextBox4.Value     
    Cells(CopyLastRow, 7).Value = TextBox5.Value    
    Unload Me    
End Sub

Answer 1

您需要指定打开的工作簿的工作表。试试下面的代码。

Dim nwb As Workbook
Dim sh As Worksheet
Dim CopyLastRow As Long

Set nwb = Workbooks.Open("D:\TestWorkbook.xlsx")
Set sh = nwb.Worksheets("Sheet1")


    CopyLastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row + 1

    sh.Cells(CopyLastRow, 1).Value = TextBox1.Value
    sh.Cells(CopyLastRow, 2).Value = TextBox2.Value
    sh.Cells(CopyLastRow, 3).Value = ComboBox1.Value
    sh.Cells(CopyLastRow, 4).Value = TextBox3.Value
    sh.Cells(CopyLastRow, 5).Value = ComboBox2.Value
    sh.Cells(CopyLastRow, 6).Value = TextBox4.Value
    sh.Cells(CopyLastRow, 7).Value = TextBox5.Value