In my VBA script, I store data in array, and I want to fill a sheet range in another Excel workbook with data from this array
Code:
Option Base 1
...
intForecastRCnt = intForecastRCnt + 1
' Save the forecast row into arrFC
ReDim Preserve arrFC(1 To 6, 1 To intForecastRCnt)
arrFC(1, intForecastRCnt) = intLine
arrFC(2, intForecastRCnt) = "" & strPartType
arrFC(3, intForecastRCnt) = "" & strTArtNo
arrFC(4, intForecastRCnt) = "FC_" & Format(datDate, "dd.mm.yyyy")
arrFC(5, intForecastRCnt) = dblFCQty
arrFC(6, intForecastRCnt) = datFCMonth
...
Set fs = CreateObject("Excel.Application")
Set fo = fs.Workbooks.Add
fo.Application.Visible = True
fo.Activate
fo.Sheets("Sheet1").Name = strTargetSheet
' Format target columns
fo.Sheets(strTargetSheet).Columns("A").NumberFormat = "General"
fo.Sheets(strTargetSheet).Columns("B:D").NumberFormat = "@"
fo.Sheets(strTargetSheet).Columns("E").NumberFormat = "General"
fo.Sheets(strTargetSheet).Columns("F").NumberFormat = "dd.mm.yyyy"
' Set headers of target columns
fo.Sheets(strTargetSheet).Range("A1").Value = strTargetH1
fo.Sheets(strTargetSheet).Range("B1").Value = strTargetH2
fo.Sheets(strTargetSheet).Range("C1").Value = strTargetH3
fo.Sheets(strTargetSheet).Range("D1").Value = strTargetH4
fo.Sheets(strTargetSheet).Range("E1").Value = strTargetH5
fo.Sheets(strTargetSheet).Range("F1").Value = strTargetH6
' Fill target datarange with values from arrFC
fo.Sheets(strTargetSheet).Range("A2:F" & intForecastRCnt + 1).Value = arrFC
...
The problem is, when using Redim on array, the parameter I have to redim must be the last one. I.e. I write an array which has 6 rows and any amount of columns. When I fill a range in Excel sheet from this array, the in 1st row of target table are written 6 values from 1st row of array, in second row are written 6 values from 2nd row of array, and so on until in 6 row of are written 6 values from 6th row of array. After that, there will be a couple of hundred table rows filled with #N/A. How to reverse data read from array?