VBA - Form that feeds a spreadsheet with values

Question

Dear, I am new in 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’m having a mistake I don’t understand how to fix..

Error shows to be in line: Lin = Ws. Cells(Rows.Count, 1). End(x1Up). Offset(1, 0). Row

Follows 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

Answer 1

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

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

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

One way to avoid typo errors is to use Option Explicit

Where an example 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.