Thread: [Solved] Fill datarange on Excel sheet from array in reverse order
View Single Post
 
Old 05-09-2023, 03:15 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
  
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default Fill datarange on Excel sheet from array in reverse order
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?
Reply With Quote