Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-09-2023, 03:15 AM
ArviLaanemets ArviLaanemets is offline Fill datarange on Excel sheet from array in reverse order Windows 8 Fill datarange on Excel sheet from array in reverse order Office 2016
Expert
Fill datarange on Excel sheet from array in reverse order
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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
  #2  
Old 05-10-2023, 10:57 PM
ArviLaanemets ArviLaanemets is offline Fill datarange on Excel sheet from array in reverse order Windows 8 Fill datarange on Excel sheet from array in reverse order Office 2016
Expert
Fill datarange on Excel sheet from array in reverse order
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Fill datarange on Excel sheet from array in reverse order 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
Fill datarange on Excel sheet from array in reverse order 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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft