VBA - Form that feeds a worksheet with values

1

Dear, I am new to VBA and for my project I am trying to make a userform that feeds a spreadsheet according to the information that the user input. I am having an error that I do not understand how to solve ..

The error is shown in the line: Lin = ws.Cells (Rows.Count, 1) .End (x1Up) .Offset (1, 0) .Row

Follow the script:

Private Sub Enter_Click()
Dim iLin As String
Dim ws As Worksheet
Set ws = Worksheets("ADAE")

lin = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row

If Trim(Me.title) = "" Then
MsgBox "Informe o Título da Planilha"
Me.title.SetFocus
Exit Sub
End If

If Not (IsNumeric(Me.nidaula)) Then
MsgBox "Digite um número válido"
Me.nidaula.SetFocus
Exit Sub
End If

If Not (Me.TM) And Not (Me.TC) Then
        MsgBox "Selecione o tipo de tarefa"
        Exit Sub
        End If

If Not (IsNumeric(Me.group)) Then
MsgBox "Digite um número válido"
Me.nidaula.SetFocus
Exit Sub
End If

ws.Cells(lin, 1).Value = Me.title.Value
ws.Cells(lin, 2).Value = Me.nidaula.Value
 If (Me.TM) Then
        ws.Cells(lin, 3) = "2"
    Else
        ws.Cells(lin, 3) = "3"
    End If
ws.Cells(l, 4).Value = Me.group.Value
ws.Columns("A:D").AutoFit

Me.title.Value = ""
Me.nidaula.Value = ""
Me.TM.Value = False
Me.TC.Value = False
Me.group.Value = ""
Me.title.SetFocus
End Sub
    
asked by anonymous 02.05.2018 / 22:11

1 answer

1

You have a typo error in the line: lin = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row

The correct would be: lin = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

That is, using .End(xlUp) with L and not .End(x1Up) with 1

  

One way to avoid typo errors is to use Option Explicit

Where a sample code can be seen below:

Option Explicit
Sub Exemplo_Option_Explicit()
    Dim lin As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Planilha1")

    lin = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'Correto
    lin = ws.Cells(ws.Rows.Count, 1).End(x1Up).Offset(1, 0).Row 'Errado
End Sub

The part of the code that has a typo or has not been declared will be highlighted.

    
02.05.2018 / 22:43