CODEWITHSUNDEEP
excelvba
finch
finch

Reputation: 549

In Excel, save one array per row, using an index

In Excel I want to post an array of 6 values to each row of an Excel worksheet, one array at a time, starting in the first column and move down the sheet a row at a time as I do this, using an index. I start at row 2, to allow for column headings.

I based my code on other code I have used in the past but it generates a compiler warning, with the line TargetRange = TargetRange.Cells(i + 1,1).Resize(1, 6).Value = Arr.

I cannot see how to use the index to step through the rows on the sheet. What should I do please?

Here is the simplified code I am using.

'~~> Excel Objects
Dim oXLApp As Object, oXLwb As Object, oXLws As Object

'Create a new Excel Application
Set oXLApp = CreateObject("Excel.Application")

'Hide Excel
oXLApp.Visible = False

'Open the relevant Excel file
Set oXLwb = oXLApp.Workbooks.Open("C:\test\test.xlsx")

'Work with Sheet1
Set oXLws = oXLwb.Sheets(1)

' Create an array to hold the cell values for posting to Excel
Dim Arr As Variant

Dim TargetRange As Range

For i = 1 to 100

    ' I set values of pProducer, pCuveee etc
    pProducer = "Fred"
    ' etc etc

    Arr = Array( _
        pProducer, _
        pCuvee, _
        pScore, _
        pLabels, _
        pVarieties, _
        pEmail _ 
        )
        
    ' Start adding cells in the second row, to leave the first row for column headings at the top of the sheet
    Set TargetRange = oXLws.Range(Cells(i + 1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False))

    ' Save company data
    TargetRange.Cells(i + 1,1).Resize(1, 6).Value = Arr

Next i

Upvotes: 1

Views: 65

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

It will be much faster to first fill the array and then output to the sheet in one instruction:

Dim Arr(1 To 100, 1 To 6)
For I = LBound(Arr, 1) To UBound(Arr, 1)
    ' fill the Arr
    For J = LBound(Arr, 2) To UBound(Arr, 2)
        Arr(I, J) = "Foo " & I & "," & J
    Next J
Next I
oXLws.Range("A2").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr

Upvotes: 1

Kin Siang
Kin Siang

Reputation: 2699

You may try amended you this following code and see if it is working, since you already use resize and loop i method, there is no need to set target range:

From:

 ' Start adding cells in the second row, to leave the first row for column headings at the top of the sheet
    Set TargetRange = oXLws.Range(Cells(i + 1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False))

    ' Save company data
    TargetRange.Cells(i + 1,1).Resize(1, 6).Value = Arr

To only (it is advised to add sheetname for cells):

Cells(i + 1, 1).Resize(1, 6) = arr

Upvotes: 1

Related Questions