Add new Row in ListObject Table with data from an Array

[Bobbo4518]

I have a Table, with 144 columns, consisting of strings that I convert to Formulas in another Table. Work around for Volatile function Indirect when I add and remove Sheets, but need the Formula NOT to go #REF....
SO - I have an 1 dim array and would need each data to be written/saved in a new row/record in a Listobject Table.

I have a working solution - but it's slow - I copy each value from the array to each cell in the table, with a "for next" loop:

Dim MyTable As ListObject
Set MyTable = Ws.ListObjects("TestTbl")
Dim NewRow As ListRow
Set NewRow = MyTable.ListRows.Add(AlwaysInsert:=True)

For i = 1 To MyTable.ListColumns.Count

     If Len(myArr(rowToCopy(s), i)) > 0 Then

         On Error Resume Next

         NewRow.Range(i).Formula = "=" & Replace(myArr(rowToCopy(s), i), "xxx", "SheetName")

         On Error GoTo 0

      Else

         NewRow.Range(i).Formula = "=" & "#N/A"

      End If

 Next i

I am looking for a way to instead work with an Array. Creating the Array and then copy the whole array in one line to the Table, into a new row. I was thinking in the lines of:

dim valueArray(1 to 144) as String

for i = 1 to 144
     If Len(myArr(rowToCopy(s), i)) > 0 Then
          On Error Resume Next
          valueArray(i).Formula = "=" & Replace(myArr(rowToCopy(s), i), "xxx", "SheetName")
          On Error GoTo 0
      Else
          valueArray(i).Formula = "=" & "#N/A"
      End If
next i

But I'm missing how to Add the new row, with each element in the Array being copied to a cell in the new row.

OBS: Im using another Table with a generic "Formula" and creates new rows with functioning formulas in the new table. Thats why im using .Formula.

 

[Akuini]

But I'm missing how to Add the new row, with each element in the Array being copied to a cell in the new row.

This is untested:

Dim MyTable As ListObject
Set MyTable = Ws.ListObjects("TestTbl")
Dim NewRow As ListRow
Set NewRow = MyTable.ListRows.Add(AlwaysInsert:=True)
Dim valueArray(1 To 144) As String

For i = 1 To 144
     If Len(myArr(rowToCopy(s), i)) > 0 Then
          On Error Resume Next
          valueArray(i).Formula = "=" & Replace(myArr(rowToCopy(s), i), "xxx", "SheetName")
          On Error GoTo 0
      Else
          valueArray(i).Formula = "=" & "#N/A"
      End If
Next i

NewRow.Range = valueArray

 

[Akuini]

Sorry, I forgot that you declared `valueArray` as a one-dimensional array, so you need to transpose it twice.

NewRow.Range = Application.Transpose(Application.Transpose(valueArray))

Another way is to declare `valueArray` as 2-dimensional array, like this:

Dim valueArray(1 To 1, 1 To 144) As String

 

[Alex Blakenburg]

Welcome to the Forum.
Without any visibility over your data it is hard for us to test your code.

Given that you said your original code was working you could see if these changes help:
Comments:
- The loop is limited by the smaller of 144 and the size of myArr ( I have assumed myArr to be the limit)
- valueArray is an array and doesn't have a property (.Formula or anything else)
- You don't need Transpose a single dimension array is copied as a row and we are copying to a row so that's fine.

For i = 1 To UBound(myArr, 2)
     If Len(myArr(rowToCopy(s), i)) > 0 Then
          On Error Resume Next
          valueArray(i) = "=" & Replace(myArr(rowToCopy(s), i), "xxx", "SheetName")
          On Error GoTo 0
      Else
          valueArray(i) = "=" & "#N/A"
      End If
Next i

NewRow.Range.Formula = valueArray
NewRow.Range.Formula = NewRow.Range.Formula

 

[Akuini]

- You don't need Transpose a single dimension array is copied as a row and we are copying to a row so that's fine.

Ah, you're right, I think I was confused because when you load a range to an array it always be a 2D array.