#1
|
|||
|
|||
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 ... |
#2
|
|||
|
|||
Creating an additional array, filling it from arrFC by reversing rows and columns, and using the new array to fill the Excel table, didn't slow the procedure down too much, and was a solution good enough. But I was surprised not finding any built-in option for this in VBA!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Print two pages per page but in reverse order | hammad | Word | 10 | 06-20-2019 09:01 AM |
reverse order of paragraphs, word 2010 | moorea21 | Word VBA | 1 | 08-02-2018 03:47 AM |
2013 search results take a long time - they fill in as results in reverse date order | themookman | Outlook | 0 | 10-11-2013 12:01 PM |
Reverse Order for Flashcards? | bknollman3 | PowerPoint | 2 | 03-23-2013 06:34 AM |
Reverse order of headings throughout Word | my_vine_figtree | Word VBA | 1 | 08-17-2010 01:46 AM |