Results 1 to 3 of 3

Thread: [Excel] Losing ListObject variable

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2023
    Posts
    4

    Post [Excel] Losing ListObject variable

    I have not run into this issue yet and I have no clue what is wrong with the following code. Using "watch" in the IDE i was able to see that tbl(i) is retrieving the correct table in the first 'Declare tables' section, but when i try to call it again in the next section of code, it says there is nothing stored in tbl(j).

    Code:
    ' Declare tables
        Dim i As Integer
        For i = 1 To sheetqty
            ReDim tbl(i) As ListObject
            Set tbl(i) = wb.Sheets(i).ListObjects(1)
        Next i
        
        ' Get unique fixtures
        Dim typesqty As Integer
        Dim rowCount As Integer
        Dim tblTemp As ListObject
        Dim j As Integer
        For j = 1 To sheetqty
            Set tblTemp = tbl(j)
            rowCount = tblTemp.ListRows.Count
            For a = 1 To rowCount
                If tblTemp.Range(a + 1, 1) <> 0 Then typesqty = typesqty + 1
            Next a
            
            ' Copy fixture quantities
            ReDim fixturetype(typesqty) As String
            ReDim fixtureqty(typesqty) As Integer
            
            For a = 1 To typesqty
                fixturetype(a) = tblTemp.Range(a + 1, 1).Value
                fixtureqty(a) = tblTemp.Range(a + 1, 2).Value
            Next a
        Next j
    Am i missing something? This should work, right?

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [Excel] Losing ListObject variable

    Code:
        For i = 1 To sheetqty
            ReDim tbl(i) As ListObject
            Set tbl(i) = wb.Sheets(i).ListObjects(1)
        Next i
    Don't do this.
    Each time you call reDim - it ERASES any existing data in the array... if sheetQty is 1, then it's not a problem... if it's 2... then you lost index 1... Just do the redim ONCE outside the loop and use sheetQty as the upper bound.
    Code:
        ReDim tbl(sheetQty) As ListObject
        For i = 1 To sheetqty
            Set tbl(i) = wb.Sheets(i).ListObjects(1)
        Next i

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2023
    Posts
    4

    Re: [Excel] Losing ListObject variable

    Ugh, i'm an idiot. Thank you for pointing that out!

Posting Permissions

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



Click Here to Expand Forum to Full Width