Code works on my computer but not any other
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Code works on my computer but not any other
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code works on my computer but not any other

    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!

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,819
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code works on my computer but not any other

    have you looked at yours and the others references
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works on my computer but not any other

    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.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,076
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code works on my computer but not any other

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works on my computer but not any other

    Quote Originally Posted by Joe4 View Post
    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?

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works on my computer but not any other

    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?

  7. #7
    New Member
    Join Date
    Mar 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works on my computer but not any other

    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-...ata-entry.html
    Last edited by Zanmato; Jul 8th, 2019 at 05:09 PM.

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works on my computer but not any other

    Quote Originally Posted by Zanmato View Post
    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-...ata-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.

  9. #9
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,819
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code works on my computer but not any other

    if you step through your code with F8 where does it fail
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code works on my computer but not any other

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •