Hi I am trying to use vba to insert a new row (to excel) and add data from an array into it. I am able to do this but the array is smaller in length than the number of cells in a row

Number of cells in single row: 12 Array : 9

The last 3 cells have formulas in them which automatically get copied when a new row is added, however the only way I can add new data is by overwriting these which comes up as #N/A

The array amongst other things consists of data like this:

tempArray(j)=Array("=HYPERLINK([@Location]," & Chr(34) & files.ListColumns("Name").DataBodyRange(i) & Chr(34) & ")",...,...,...,...)

I know if I have static unrelated formulas (non "@") that I can do away with ListRows.Add which is what I was doing but now I need related formulas.

I have tried various things like:

    With data.ListRows.Add
    
    'Set newRng = .Range.Range(.Range.Cells(1, 1), .Range.Cells(1, 9))

    .Range.Range(.Range.Cells(1, 1), .Range.Cells(1, 9)).Value2 = tempArray(i)
    .Range.Value2 = tempArray(i)
         
    'data.Range(data.Cells(lr   1, 1), data.Cells(lr   1, UBound(tempArray(i))   1)).Value = tempArray(i)
        
        lr = lr   1
        
    End With

I know this answer relates to something similar, but I want to apply it all in one go as opposed to one by one

CodePudding user response：

You can resize the range of the listrow according to the size of your tempArray:

Dim lr as ListRow   'I prefer to use a single variable for readability reasons
With data
    Set lr = .ListRows.Add
    lr.Range.Resize(, UBound(tempArray)   1).Value = tempArray
End With