Excel vba简单文本框插入子错误

Question

我正在制作一个非常简单的应用程序来插入名称和其他一些信息，但我在 sub.xml 中遇到了问题。不知道怎么回事，好久没用vba了。。。。

    Private Sub button_Click()

Dim linha As Long

   linha = Worksheets("FAMINHO_ESCOLAS").cell(Rows.Count, 1).End(xlUp).Row + 1
   
   Range("A" & linha).Value = boxname.Value
   Range("B" & linha).Value = boxinstr.Value
   Range("C" & linha).Value = boxescola.Value
   Range("D" & linha).Value = boxtel.Value
   Range("E" & linha).Value = boxemail.Value

End Sub

我收到错误 438
我正在尝试返回值，当我按下“buttonright”时，它会更改为下一个数据，当我按下 buttonleft 时，它会显示以前的数据等等

Private Sub CommandButton1_Click()

GetFAMINHO_ESCOLASLastRow boxname1.Value, boxinstr1.Value, boxescola1.Value, 
boxtel1.Value, boxemail1.Value

End Sub

Function GetFAMINHO_ESCOLASLastRow() As Range
    Dim Target As Range
    With Worksheets("FAMINHO_ESCOLAS")
        Set Target = .Cells(.Rows.Count, 1).End(xlUp)
        Set Target = Intersect(Target.EntireRow, Target.CurrentRegion)
    End With
    
    Set GetFAMINHO_ESCOLASLastRow = Target
End Function

Answer 1

linha 设置为最后一行，但 LR是实际用于最后一行的变量。

linha = Worksheets("FAMINHO_ESCOLAS").Cell(Rows.Count, 1).End(xlUp).Row + 1

Cell(应更改为 Cells( .

linha = Worksheets("FAMINHO_ESCOLAS").Cells(Rows.Count, 1).End(xlUp).Row + 1

最好有资格 Rows.Count到工作表。
我更喜欢编写一个单独的子例程来添加值。通过这种方式，我可以测试代码而无需实例化用​​户窗体。
替代解决方案
注: AddRowToFAMINHO_ESCOLAS将接受从 1 到 69 的任何值。

Private Sub button_Click()
    AddRowToFAMINHO_ESCOLAS boxname.Value, boxname.Value, boxinstr.Value, boxescola.Value, boxtel.Value, boxemail.Value
End Sub


Sub AddRowToFAMINHO_ESCOLAS(ParamArray Args() As Variant)
    With Worksheets("FAMINHO_ESCOLAS")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(1, UBound(Args) + 1).Value = Args
    End With
End Sub

AddRowToFAMINHO_ESCOLAS演示

附录
此函数将返回 A 列中具有值的最后一行。

Function GetFAMINHO_ESCOLASLastRow() As Range
    Dim Target As Range
    With Worksheets("FAMINHO_ESCOLAS")
        Set Target = .Cells(.Rows.Count, 1).End(xlUp)
        Set Target = Intersect(Target.EntireRow, Target.CurrentRegion)
    End With
    
    Set GetFAMINHO_ESCOLASLastRow = Target
End Function

您可以通过在即时窗口中输入以下代码来测试此功能:

Application.Goto GetFAMINHO_ESCOLASLastRow

对问题更新的回应
我做了一些改动，因为 OP 想要写入和检索这些值。

Private Sub buttonleft_Click()
    Dim Target As Range
    Set Target = GetFAMINHO_ESCOLASLastRow
    With Target
        boxname.Value = .Cells(1, 1).Value
        boxinstr.Value = .Cells(1, 2).Value
        boxescola.Value = .Cells(1, 3).Value
        boxtel.Value = .Cells(1, 4).Value
        boxemail.Value = .Cells(1, 5).Value
    End With
End Sub

Private Sub buttonright_Click()
    Dim Target As Range
    Set Target = GetFAMINHO_ESCOLASNewRow
    With Target
        .Cells(1, 1).Value = boxname.Value
        .Cells(1, 2).Value = boxinstr.Value
        .Cells(1, 3).Value = boxescola.Value
        .Cells(1, 4).Value = boxtel.Value
        .Cells(1, 5).Value = boxemail.Value
    End With
End Sub


Function GetFAMINHO_ESCOLASLastRow() As Range
    Dim Target As Range
    With Worksheets("FAMINHO_ESCOLAS")
        Set Target = .Cells(.Rows.Count, 1).End(xlUp)
        Set Target = Intersect(Target.EntireRow, Target.CurrentRegion)
    End With
    
    Set GetFAMINHO_ESCOLASLastRow = Target
End Function


Function GetFAMINHO_ESCOLASNewRow() As Range
    Set GetFAMINHO_ESCOLASNewRow = GetFAMINHO_ESCOLASLastRow.Offset(1)
End Function