CODEWITHSUNDEEP
excelvbastringreferencelistobject
thomasb25
thomasb25

Reputation: 13

Array of String Cell-References Passed into New Row of Table is Not Displaying Referenced Values

I have a script that arranges user-fillable data into item slots in a sheet, while having that same data represented in another sheet as a .

Each time a slot is generated, each cell in that slot is named and then those names are passed via a of arrays into function that then inserts the as a New Row in my .

    Dim str_line(5) As String

    str_line(0) = rng_sid.Name
    str_line(1) = rng_status.Name
    str_line(2) = rng_width.Name
    str_line(3) = rng_height.Name
    str_line(4) = rng_material.Name
    str_line(5) = rng_quantity.Name
    
    Call modData.AddTableLine(str_line)

Sub AddTableLine(str_line() As String)

    Dim table As ListObject
    Dim newRow As ListRow
    
    Set table = Range("tblData").ListObject
    Set newRow = table.ListRows.add(AlwaysInsert:=True)
    
    newRow.Range = str_line
    
End Sub

The problem I'm facing is that when the new row is inserted into the , the table is not recognizing that these are formulas and are displaying simply the value.

Table displaying absolute cell references]

Just to be clear, all I'm trying to do here is get the cell-references to work properly. Something is happening when I inject them into my table that is causing them to be read as text instead of a formula.

I've tried a few different things, like cycling through the array for each individual value, which works except for some reason that turns every cell in its column into the same reference.

I've also tried Application.CalculateFullRebuild, which doesn't appear to have any effect whatsoever.

Another oddity is how I pass cell names into the array, but when they are inserted into the table, they are instead passed as absolute cell references.

Seems that the issue is related to something about the pasting strings and not recognizing them as formulas. I don't think I've had this be an issue before, but I'm pretty green with using a in VBA.

Upvotes: 1

Views: 41

Answers (1)

Ike
Ike

Reputation: 13024

This will work - as you only have five values it shouldn't be a performance issue:

Sub AddTableLine(str_line() As String)

    Dim table As ListObject
    Dim newRow As ListRow
    
    Set table = Tabelle2.ListObjects(1)
    Set newRow = table.ListRows.Add(AlwaysInsert:=True)
    
    Dim fResetAutoFill As Boolean
    With Application.AutoCorrect
        If .AutoFillFormulasInLists = True Then
            .AutoFillFormulasInLists = False
            fResetAutoFill = True
        End If
    End With
    
    
    Dim i As Long
    For i = 0 To 5
        newRow.Range(1, i + 1).Formula = str_line(i)
    Next
    
    With Application.AutoCorrect
        If fResetAutoFill = True Then
            .AutoFillFormulasInLists = True
        End If
    End With
   
End Sub

Upvotes: 0

Related Questions