Creating a new row problem

[luizmachado]

I have this code that should send all the informations I gave on the userform (previouly opened with a button) to the right place on the last row.
For example,

First entry (row 23: first "last row"):
I click on "Inserir dados" (Fill userform) > it opens the userform > I fill the textbox beside "Descricao" with "test1" and click submit > The code shall make this "test1" goes to C & lastrow -1

All the entries ahead (it may be limited, for example, from row 24 -second row- to row 20000 -I probaly won't reach this many rows-):
I click on "Inserir dados" (Fill userform) > it opens the userform > I fill the textbox beside "Descricao" with "test2" and click submit > The code shall create another row and then make this "test2" goes to C & lastrow -1

I've tried everything and don't know what to do. Do you guys know what I can do to solve it?


This is the code (actually there's much more than just that. I simplified in order to make the tests easier) - I can't post the code on tags (it gets messed up):

Private Sub submit_Click()

    Dim lrTest As Long

'Count last row
    lrTest = Sheets("Monthly Expenses").Range("B" & Rows.Count).End(xlUp).Row
    If lrTest = 23 Then

        'Description TextBox
            Range("C" & lrTest - 1).value = TextBox1.Text

        'Resize table row from B21 to J on the last row
        ActiveSheet.ListObjects("Table1").Resize Range("$B$21:$J$" & lrTest + 1)

'Select A1
        Range("A1").Select

    Else
        'Count last row
        lrTest = Sheets("Monthly Expenses").Range("B" & Rows.Count).End(xlUp).Row

'Description TextBox
            Range("C" & lrTest - 1).value = TextBox1.Text

        'Resize table row from B21 to J on the last row
        ActiveSheet.ListObjects("Table1").Resize Range("$B$21:$J$" & lrTest + 1)

        'Select A1
        Range("A1").Select
End If
End Sub

Private Sub UserForm_Initialize()
'Clear category and add options to category
    cmbcategoria.Clear
    With cmbcategoria
        .AddItem "Casa"
        .AddItem "Lazer"
        .AddItem "Mercado"
        .AddItem "Saúde e Higiene"
        .AddItem "Servicos"
        .AddItem "Snoopy e Bob"
        .AddItem "Suporte"
        .AddItem "Vestuário"
        .AddItem "Outros"
    End With
End Sub

Private Sub cancel_Click()
'Close UserForm
    Unload Me
End Sub

This is how my workbook looks by now:
Since I can't upload any image, I'm linking it below.
i.imgsafe.org/eb90f7b.png

Ps: I can't link the other posts either, the only thing I can do is tell you that I have 2 more posts on "ExcelForum" and "MrExcel" with the same title.

[luizmachado]

I thinking of use IsEmpty function. But the code below is filling the lastrow - 1 (that's ok) and then creating another row (that's what I'm trying to avoid). I'd like this to have only rows that are filled, I mean, no extra blank row.


This is the code I've just tried:

Private Sub submit_Click()

Dim lrTest As Long

'Count last row
lrTest = Sheets("Monthly Expenses").Range("B" & Rows.Count).End(xlUp).Row
If IsEmpty(Range("C" & lrTest - 1)) Then
'Description TextBox
Range("C" & lrTest - 1).value = TextBox1.Text
End If
'Resize table row from B21 to J on the last row
ActiveSheet.ListObjects("Table1").Resize Range("$B$21:$J$" & lrTest + 1)
End Sub

Ps: sorry for "double posting". I'm just trying to help you guys to help me.

[SamT]

If Lr = 23 then Range("C" & lrTest - 1) = Range("C22")

Try

Range("C" & lrTest + 1).value = TextBox1.Text

[luizmachado]

I tested it and it's jumping one line. For example: if I enter with "test1" it goes to the first row (C22), but when I enter with "test2" it won't go anywhere. It repeats with test3, test4, etc.

I guess I just solved it. First I thought (still think) it was a bug (a good bug haha), but then a added and condition (If) that "breaks" this bug. I mean, when this bug doesn't work (the first row -C22- was the problem) and everything goes to C23, it'll move it to C22 and delete the last row - 1.

This is the code:

Private Sub submit_Click()

    Dim lrTest As Long


    'Count last row
    lrTest = Sheets("Monthly Expenses").Range("B" & Rows.Count).End(xlUp).Row
    If IsEmpty(Range("C" & lrTest - 1)) Then
        'Description TextBox
        Range("C" & lrTest + 1).value = TextBox1.Text
    End If
        'Resize table row from B21 to J on the last row
        ActiveSheet.ListObjects("Table1").Resize Range("$B$21:$J$" & lrTest + 1)
End Sub

[SamT]