Finally tracked down the source/reason responsible for how I keep getting this error... now I need to understand WHY its happening...(?)

[kbishop94]

The error specifically is this: Run-time error ' 1004' Insert method of Range class failed


It has frustrated me till no end because I couldn't make heads nor tails out of why it was occurring for the specific sub where it was happening when I also had the same EXACT code in another sub that does the exact same thing and it never trips up an error and has always worked fine... hmm.

First, here is what the code does... (hopefully this will make it easier to follow along with how and why I have the code the way I do...) (hopefully! )

On a userform for entering a new record, the user has a list of names in a combobox that they can select from:

If the name that they need to enter isn't found in the combobox list, then they can hit another command button (indicated above by the little red arrow) that will open a 'popup' form where the new name can be entered.

This will open up a popup form where the new name can be added:


After entering a new name, and hitting the 'Add The New Name' command button, the code adds the new entry to the specific 'named table range' (which is "Table2"), then closes out the 'popup form' and adds the new name to the combobox field that they were on.

Its upon clicking on the 'Add The New Name" button where the code breaks.

To cut to the chase, the issue turned out to be that when I have 2 named table ranges next to each other AND on the same worksheet, I get the error.

And here is the error and the line where it breaks:

(edit... the code above references "Table24"... this is really Table2. I was just changing many things around trying to find out why/how the error was occurring and I copied the code from one of those changes.)

Here is the worksheet where the two named table ranges are and what they look like: (Where the 'named table range' in column A is "Table1", and the one in column B is "Table2")


I finally figured out the problem after trying all kinds of different ways to execute the code. What finally allowed the code to work, was when I moved one of the named ranges onto a separate worksheet, everything then suddenly worked just fine. (and didn't matter which named table range either, just moving either one of them onto a different sheet allowed both of them to work.)

When I would go to move the other one back to the original sheet, it went right back to not allowing one of them not to work... btw, it was always "Table1" that always seemed to work fine and "Table2" was the one that would always trip up the error.

So it looks like (I think this is why) the reason why its always Table2 that trips the error is because its the shorter of the two. (Table1 goes to row 169 and Table2 only goes to 74)


And finally, here is the code from the screenshot above. This code is from the popup-form for adding a new name to be added to the list. And this is where the break occurs when, if i have both of the named table ranges on the same worksheet, it trips the error when it gets to the ".Rows(k).EntireRow.Insert" line.
Again, just to be clear, both sets of code (for both popup forms and their respective named table ranges), will work fine IF they are not on the same worksheet. If they are on different sheets, then everything runs just fine.

This isn't very efficient (as well as not making much sense to me) and I would like to figure out how to fix this so that they both can be on the same worksheet and not trip up any errors.

Many, many thanks for any help/suggestions anyone can offer.

Private Sub cmdAddName_Click()
Dim ws As Worksheet
Set ws = Worksheets("LookUpLists")

Dim k As Long
Dim d As Long

With Sheets("LookUpLists")
    k = .Range("Table2").Row
    d = .Range("Table2").Column
   
    Do While Not IsEmpty(.Cells(k, d))
        k = k + 1
    Loop
   
    .Rows(k).EntireRow.Insert
    .Rows(k).EntireRow.FillDown

.Cells(k, d + 0).value = Me.txtNewNAME.value

With Sheets("LookUpLists").ListObjects("Table2")
    .Resize .Range.Resize(.Range.Rows.Count + 1)
End With

ActiveWorkbook.Worksheets("LookupLists").Activate
With ActiveWorkbook.Worksheets("LookupLists").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("Table2").Columns, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range("Table2")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

MsgBox "The new NAME you entered: " & UCase(Me.txtNewNAME) & vbNewLine & "has been added to the NAME List database." & vbNewLine & " " & vbNewLine & "Please reopen the 'ENTER NEW INCIDENT' form to re-enter your incident."
frmIncidentEntry.cboIssuedBy.value = Me.txtNewNAME.value
Unload Me

End With
End Sub

 

[kbishop94]

I got it... (and I cant believe I didnt see this... )

The problem was that I had it trying to insert an entire row (which effected the other named table range that was in the column next to it.)

So I changed this:

.Rows(j).EntireRow.Insert

To this:

With Sheets("NameSheet").ListObjects("Table24")
        i = ActiveCell.Row - .HeaderRowRange.Row
        .DataBodyRange.Cells(i + 1, 1) = .DataBodyRange.Cells(i, 1)
End With

 

[jxb]

Just out of interest / curiosity
The loop while Not Isempty() is just looking for the last row in table 2 ?

 

[Domenic]

Just out of interest / curiosity
The loop while Not Isempty() is just looking for the last row in table 2 ?

Actually, it's looking for the first blank cell...

 

[jxb]

Thanks. Just learning and always enjoy picking up new ways of coding stuff. My coding is not always (never !) the most efficient- being self taught and all that but it does the job

Would it be possible to use xldown then? from the 1st row of table 2 (which you know)
John

 

[Domenic]

Would it be possible to use xldown then? from the 1st row of table 2 (which you know)

You can, but you'll need to be careful. Let's say we have the following data...

Book1
	A	B	C
1		Data
2		A
3		B
4		C
5		D
6		E
7		F
8		G
9
Sheet1

Range("B2").End(xlDown).Row returns 8 as the last row. However, let's say we have the following data instead...

Book1
	A	B	C
1		Data
2		A
3		B
4		C
5
6		E
7		F
8		G
9
Sheet1

This time Range("B2").End(xlDown).Row returns 4 instead. So, in this example, to return 8 as the last used row, you can use the following instead...

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
  
    Debug.Print lastRow

Hope this helps!

 

[jxb]

Quite true but you stated that the loop was looking for the first blank cell so would returning row 4 in your example not be correct?

 

[Domenic]

Quite true but you stated that the loop was looking for the first blank cell so would returning row 4 in your example not be correct?

In the original code, yes it's looking for the first blank cell. However, it looks like to me that the intent by the original poster is to find the next available row after the last used row. And since the data contains no empty cells, the method being used will find the next available row after the last used row. So the method to be used depends on the data, and one's desired outcome.

 

[kbishop94]

That is correct sir