Code works on my computer but not any other

AnotherExcelGuy

New Member
Joined
Jul 8, 2019
Messages
12
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'

Code:
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!
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,368
Office Version
2019, 2016, 2013
Platform
Windows
have you looked at yours and the others references
 

AnotherExcelGuy

New Member
Joined
Jul 8, 2019
Messages
12
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,746
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 8, 2019
Messages
12
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

New Member
Joined
Jul 8, 2019
Messages
12
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

New Member
Joined
Mar 15, 2019
Messages
14
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
 
Last edited:

AnotherExcelGuy

New Member
Joined
Jul 8, 2019
Messages
12
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

Moderator
Joined
Oct 23, 2004
Messages
10,368
Office Version
2019, 2016, 2013
Platform
Windows
if you step through your code with F8 where does it fail
 

AnotherExcelGuy

New Member
Joined
Jul 8, 2019
Messages
12
It fails at line 41. That's the second time this line shows up:

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

Watch MrExcel Video

Forum statistics

Threads
1,102,868
Messages
5,489,371
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top