VBA insert data next row if exists-CodePudding

(VBA Beginner) Row 15 is my first row of the table. I can add data using a UserForm into Row 15 but i want to if i have data in Row 15 add into the next row (in my case Row 16 always 1) this is the function that im using:

Public Function GetLastRow(TargetWorksheet As Worksheet, ColumnNo As Variant) As Long
    If TargetWorksheet Is Nothing Then Exit Function
    GetLastRow = TargetWorksheet.Cells(TargetWorksheet.Rows.Count, ColumnNo).End(xlUp).Row
End Function

And this is my add button:

Private Sub CommandButton1_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("OIL_")
    Dim GetLastRow As Long
    'Validações-----------------------------------------------------
    If Me.txtID.Value = "" Then
    MsgBox "Insira um ID!", vbCritical
    Exit Sub
    End If
    If Me.txtDesc.Value = "" Then
    MsgBox "Insira uma Descrição!", vbCritical
    Exit Sub
    End If
    '---------------------------------------------------------------
    If GetLastRow   1 <= 15 Then GetLastRow = 15 Else GetLastRow = GetLastRow   1
    sh.Cells(GetLastRow, 2).Value = Me.txtID.Value
    sh.Cells(GetLastRow, 11).Value = Me.txtDesc.Value
    sh.Cells(GetLastRow, 29).Value = Me.txtData.Value
End Sub

For now its only inserting data into my Row 15 and if i try to add another one it just replaces for the new data, i tried a lot of things and i cant manage to add data into the next row always.

CodePudding user response：

Your function is named the same as your variable, this is confusing and if you debug your code you will find the GetLastRow will always equal zero and that is causing your issue. My biggest suggestion is to learn to use the debugger!

You could change your code to something like this:

Private Sub CommandButton1_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("OIL_")
    Dim lastRow As Long
    'Validações-----------------------------------------------------
    If Me.txtID.Value = "" Then
    MsgBox "Insira um ID!", vbCritical
    Exit Sub
    End If
    If Me.txtDesc.Value = "" Then
    MsgBox "Insira uma Descrição!", vbCritical
    Exit Sub
    End If
    '---------------------------------------------------------------
    lastRow = GetLastRow(sh, 3) 'I am not sure what column you need I just used 3 for example
    If lastRow   1 <= 15 Then lastRow = 15 Else lastRow = lastRow   1
    sh.Cells(lastRow, 2).Value = Me.txtID.Value
    sh.Cells(lastRow, 11).Value = Me.txtDesc.Value
    sh.Cells(lastRow, 29).Value = Me.txtData.Value
End Sub

CodePudding user response：

Well, problem solved this is the right way to do it

Private Sub AddButton_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("OIL_")
    Dim lastRow As Long
    'Validações-----------------------------------------------------
    If Me.txtID.Value = "" Then
    MsgBox "Insira um ID!", vbCritical
    Exit Sub
    End If
    If Me.txtDesc.Value = "" Then
    MsgBox "Insira uma Descrição!", vbCritical
    Exit Sub
    End If
    '---------------------------------------------------------------
    lastRow = GetLastRow(sh, 2)
    If lastRow   1 <= 15 Then lastRow = 15 Else lastRow = lastRow   1
    sh.Cells(lastRow, 2).Value = Me.txtID.Value
    sh.Cells(lastRow, 11).Value = Me.txtDesc.Value
    sh.Cells(lastRow, 29).Value = Me.txtData.Value
End Sub

And this is the right function:

Public Function GetLastRow(TargetWorksheet As Worksheet, ColumnNo As Variant) As Long
    If TargetWorksheet Is Nothing Then Exit Function
    GetLastRow = TargetWorksheet.Cells(TargetWorksheet.Rows.Count, ColumnNo).End(xlUp).Row
End Function

Thanks to everyone