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]

:hi: