Code works on my computer but not any other

[AnotherExcelGuy]

Hi all,

Having some trouble and not sure what to try next. I'm getting the following run time error on other people's computers but it works fine on mine: '-2147417848 (80010108)': Method 'Add' of object 'ListRows' failed


Here's my code, please excuse the rough coding as I'm very new at this and this took me a long time to figure out. I put this under a 'Module'

Option Explicit

Sub CreateProjects()
    ' Dim lCount As Integer
    Dim lMonthCount As Integer
    Dim x As Integer, xProjects As Integer
    Dim lNewRow As ListRow
    Dim lProjectsCount As Integer
    FreezeApp
    Sheets("Data Entry").Visible = True
    
    ClearTable
       
    
    lMonthCount = DateDiff("m", ThisWorkbook.Sheets("Projects").Range("G2"), ThisWorkbook.Sheets("Projects").Range("h2"))
    lProjectsCount = GetAllTableRows("Projects", "tbProjects")
        
    
    For xProjects = 2 To lProjectsCount + 1
        ' Header
        Set lNewRow = ThisWorkbook.Sheets("Data Entry").ListObjects("Table14").ListRows.Add
        lNewRow.Range(1) = ThisWorkbook.Sheets("Projects").Range("A" & xProjects)
        lNewRow.Range(3) = ThisWorkbook.Sheets("Projects").Range("C" & xProjects)
            
            lNewRow.Range(2) = ThisWorkbook.Sheets("Projects").Range("B" & xProjects)
            lNewRow.Range(4) = ThisWorkbook.Sheets("Projects").Range("D" & xProjects)
            lNewRow.Range(5) = ThisWorkbook.Sheets("Projects").Range("E" & xProjects)
            lNewRow.Range(6) = ThisWorkbook.Sheets("Projects").Range("F" & xProjects)
            lNewRow.Range(9) = ThisWorkbook.Sheets("Projects").Range("G" & xProjects)
            lNewRow.Range(10) = ThisWorkbook.Sheets("Projects").Range("H" & xProjects)
            lNewRow.Range(13) = ThisWorkbook.Sheets("Projects").Range("F" & xProjects)
            lNewRow.Range(15) = ThisWorkbook.Sheets("Projects").Range("G" & xProjects)
            lNewRow.Range(16) = ThisWorkbook.Sheets("Projects").Range("H" & xProjects)
        
            lNewRow.Range(22) = ThisWorkbook.Sheets("Projects").Range("I" & xProjects)
            lNewRow.Range(23) = ThisWorkbook.Sheets("Projects").Range("J" & xProjects)
        
        
        lMonthCount = DateDiff("m", ThisWorkbook.Sheets("Projects").Range("G" & xProjects), ThisWorkbook.Sheets("Projects").Range("H" & xProjects))
        For x = 1 To lMonthCount
            Set lNewRow = ThisWorkbook.Sheets("Data Entry").ListObjects("Table14").ListRows.Add
            
            lNewRow.Range(1) = ThisWorkbook.Sheets("Projects").Range("A" & xProjects)
            lNewRow.Range(3) = ThisWorkbook.Sheets("Projects").Range("C" & xProjects)
            lNewRow.Range(2) = ThisWorkbook.Sheets("Projects").Range("B" & xProjects)
            lNewRow.Range(4) = ThisWorkbook.Sheets("Projects").Range("D" & xProjects)
            lNewRow.Range(5) = ThisWorkbook.Sheets("Projects").Range("E" & xProjects)
            lNewRow.Range(6) = ThisWorkbook.Sheets("Projects").Range("F" & xProjects)
            lNewRow.Range(9) = ThisWorkbook.Sheets("Projects").Range("G" & xProjects)
            lNewRow.Range(10) = ThisWorkbook.Sheets("Projects").Range("H" & xProjects)
            
            lNewRow.Range(13) = -1 * ThisWorkbook.Sheets("Projects").Range("F" & xProjects) / lMonthCount
            
            lNewRow.Range(22) = ThisWorkbook.Sheets("Projects").Range("I" & xProjects)
            lNewRow.Range(23) = ThisWorkbook.Sheets("Projects").Range("J" & xProjects)
        
            
        Next x
    Next xProjects
    ' lCount = GetAllTableRows("Data Entry", "Table14")
    ' ThisWorkbook.Sheets("Data Entry").ListObjects("Table14").ListRows.Add(
    
' lNewRow.Range(14) = "=SUM($N$3:N3)"
    ThisWorkbook.Sheets("Data Entry").ListObjects("Table14").ListRows(1).Range(14).FormulaArray = "=IF(RC[-1]="""","""",SUM(R3C14:RC[-1]))"
    ThisWorkbook.Sheets("Data Entry").ListObjects("Table14").ListRows(1).Range(19).FormulaArray = "=IF(RC[-1]="""","""",SUM(R3C19:RC[-1]))"
    
    Sheets("Data Entry").Visible = False
    
    UnFreezeApp
    ActiveWorkbook.RefreshAll
End Sub

Function GetTableRows(pSheet As String, pTableName As String) As Integer
    'GetTableRows = pSheet.ListObjects(pTableName).Range.Rows.Count - 1
    GetTableRows = ThisWorkbook.Sheets(pSheet).ListObjects(pTableName).Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
End Function


Function GetAllTableRows(pSheet As String, pTableName As String) As Integer
    GetAllTableRows = ThisWorkbook.Sheets(pSheet).ListObjects(pTableName).Range.Columns.Rows.Count - 1
End Function

Private Sub FreezeApp()
    Application.ScreenUpdating = False
End Sub

Private Sub UnFreezeApp()
    Application.ScreenUpdating = True
End Sub


Private Sub ClearTable()
    Sheets("Data Entry").Select
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Sheets("Display").Select
    Range("A1").Select
End Sub

Any help would be greatly appreciated - thank you!

 

[mole999]

have you looked at yours and the others references

 

[AnotherExcelGuy]

Hi - yes, it seems that we both have the same references setup - table names, worksheet names, defined names, etc. Is there anything else I can check? We are using the exact same file.

 

[Joe4]

No, I think he means VBA References (Libraries).

From the VB Editor, go to the Tools menu and select References.
Check to see all the items that you have selected there on your computer.
Do the same thing on the computer that it is not working on.
Are they missing any selections that you have? If so, select them and see if that fixes the issue.

 

[AnotherExcelGuy]

No, I think he means VBA References (Libraries).

From the VB Editor, go to the Tools menu and select References.
Check to see all the items that you have selected there on your computer.
Do the same thing on the computer that it is not working on.
Are they missing any selections that you have? If so, select them and see if that fixes the issue.

I just checked and the references are all the same.

I also realized that my script is only working on computers that have Excel 64 bit, I'm only getting the error on computers that have Excel 32 bit. Has anyone ever seen this before?

 

[AnotherExcelGuy]

Just as an Update - My code only works on computers with the 64 bit version of Excel, not the 32 bit version of Excel. I'm only getting the error I mentioned on the 32 bit version of Excel. Does anybody know I can change my code so it works on both?

 

[Zanmato]

Very odd. The computers you've tried it on which are using 32 bit excel, do they have the same version? (2013-2016? 2010? etc). There's definitely not any code stored in another module or userform or anything that you didn't include in the post?

Also I assume the file you're working with isn't insanely large (ie several GB) in size?

EDIT: Just found this thread on another site, which then links to a different thread on this site, both/either of which might be of help. I tried reading through it all but some of it went right over my head I'm afraid, maybe you (or someone else here) will be able to use this info to fix your problem. Good luck.

https://social.msdn.microsoft.com/Forums/office/en-US/6fbcfa0f-e1d0-49e9-b96d-7c390fcf61a2/method-add-of-object-listrows-failed?forum=exceldev
https://www.mrexcel.com/forum/excel-questions/537680-excel-2010-crashing-userform-data-entry.html

 

[AnotherExcelGuy]

Very odd. The computers you've tried it on which are using 32 bit excel, do they have the same version? (2013-2016? 2010? etc). There's definitely not any code stored in another module or userform or anything that you didn't include in the post?

Also I assume the file you're working with isn't insanely large (ie several GB) in size?

EDIT: Just found this thread on another site, which then links to a different thread on this site, both/either of which might be of help. I tried reading through it all but some of it went right over my head I'm afraid, maybe you (or someone else here) will be able to use this info to fix your problem. Good luck.

https://social.msdn.microsoft.com/Forums/office/en-US/6fbcfa0f-e1d0-49e9-b96d-7c390fcf61a2/method-add-of-object-listrows-failed?forum=exceldev
https://www.mrexcel.com/forum/excel-questions/537680-excel-2010-crashing-userform-data-entry.html

Yes - the computers I've tried that are running on 32 bit excel is the same computer that I have running 64 bit excel 2016, Windows 7. The only real difference is that I upgraded to 64 bit excel. I've tried 3 computers with 64 bit excel and my program runs - I've tried 2 comuters with 32 bit exel and I get the error, it does not crash.

The file I am working with is very small - just sample data for now.

Thanks for the link, I will review and try to figure something out.

 

[mole999]

if you step through your code with F8 where does it fail

 

[AnotherExcelGuy]

It fails at line 41. That's the second time this line shows up:

Set lNewRow = ThisWorkbook.Sheets("Data Entry").ListObjects("Table14").ListRows.Add