Excel-VBA Listrow.add not inserting row

Question

I have a bit of code that checks a userform for data in a combobox. Then if found adds a row to the table. Unfortunately, I am finding that some times it does not add the row and just overwrites the line. Any ideas on what I have done wrong would help. The following is the code for the portion that creates the new line:

If Not vbNullString = Me.CrimeSN2.Value Then
    If Not vbNullString = Me.RowNumber.Value Then
        rownumval = Me.RowNumber.Value
        Worksheets("Offense").ListObjects("OffenseTable").ListRows.Add (rownumval)
        rownumval = rownumval + 1
    Else
        rownumval = LastRow + 1
        Worksheets("Offense").ListObjects("OffenseTable").ListRows.Add (rownumval)
        rownumval = rownumval + 1
end if

Does it just overwrite the last row of your table? Does it happen during the If or the Else part of your code, or both? Is LastRow correct when you step through your code using F8? — Doug Glancy
– Doug Glancy, Commented Apr 20, 2016 at 22:25
You check if the cell value (Me.RowNumber.Value) is not Null, however, it may not be a number. If it's not a number, the .Add will fail. — OldUgly
– OldUgly, Commented Apr 20, 2016 at 23:38
Thanks for the response Doug and OldUgly, to respond to your inquiry. It overwrites at the location of the rownumval+1. I stepped through the code and it only overwrites on the If portion. and LastRow is correct. I have stepped through it and it reads rownumval as a long and has value. it just seems to not work on the .ListRows.Add (rownumval). To Old Ugly Me.RowNumber.Value is reading as long and has value. I did try the code you provided but still continues with the error. — fatboyzfishing
– fatboyzfishing, Commented Apr 21, 2016 at 15:38

OldUgly · Accepted Answer · 2016-04-21 00:00:44Z

1

This will provide further protection against having a non-number in rownumval

If Not vbNullString = Me.CrimeSN2.Value Then
    If Not vbNullString = Me.RowNumber.Value And IsNumeric(Me.RowNumber.Value) Then
        rownumval = Me.RowNumber.Value
        Worksheets("Offense").ListObjects("OffenseTable").ListRows.Add (rownumval)
        rownumval = rownumval + 1
    Else
        rownumval = LastRow + 1
        Worksheets("Offense").ListObjects("OffenseTable").ListRows.Add (rownumval)
        rownumval = rownumval + 1
end if

answered Apr 21, 2016 at 0:00

OldUgly

2,1393 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel-VBA Listrow.add not inserting row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related