Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: Excel VBA Data Location
I wrote the module "Table2Excel2()" to replace "Table2Excel1()" to improve the running speed.
However, the data loaded to Excel sheet by module "Table2Excel2()" is same to that of "Table2Excel1()" but is started 1 more row & column (cell P4 but expect O3). I use the code Debug.Print to ensure the data of LTable & MTable are the same. Please advise. Many thanks.
Public LTable(290000, 11) As Variant	'LTable() filled by other module

Sub Table2Excel1()
    Dim RowPtr As Long
    Dim i As Integer
    With Sheets(1)
        For RowPtr = 3 To 290000
            For i = 1 To 8
                .Cells(RowPtr, i + 14) = LTable(RowPtr, i)
            Next i
        Next RowPtr
    End With
    Debug.Print LTable(22, 8) & ","; LTable(3, 2)	'check content of LTable()
End Sub
Public MTable As Variant	'MTable() filled by other module

Sub Table2Excel2()
    Sheets(1).Range("o1", "v" & 290000) = MTable
    Debug.Print MTable(22, 8) & ","; MTable(3, 2)	'check content of MTable()
End Sub
Posted 29-Aug-12 8:36am
Edited 29-Aug-12 9:04am
Kenneth Haugland at 29-Aug-12 14:30pm
WHy did you expect one to be faster than the other?
tv1owner at 29-Aug-12 15:08pm
Hi Kenneth Haugland,
The module "Table2Excel2()" run faster since it doesn't write to the cell 1 by 1. Please note.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

First of all, read about MS Excel's objects[^], because Sheet object and Worksheet object is not the same.
The best programming practice is to use code with context. To explain what i mean, do it:
1) Open or create few MS Excel Workbooks,
2) Activate first workbook and run this code:
For i = 1 To 30
    Sheets(1).Range("A" & i) = i
Next i
3) Then activate another workbook and run above code
4) and so on...
What kind of results you can expect?
Each first worksheet for each workbook is filled in with values 1 to 30 in column A.
To properly load data in to correct worksheet, you need to do something like this:
Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets(1)
wsh.Range("A1") = 5
No matter how many times you'll be run the above code and no matter which workbook is active, the above code is running with context and insert the data into correct workbook/worksheet (only into this workbook where the code is placed).
I do not understand what you are trying to do, but if you update your question (use Improve question widget) and explain the exact problem, i'll upgrade my solution. Please, provide more details about used arrays and stored data.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 645
1 Maciej Los 225
2 Abhinav S 171
3 Richard Deeming 160
4 Suvendu Shekhar Giri 159
0 Sergey Alexandrovich Kryukov 9,623
1 OriginalGriff 9,095
2 Peter Leow 5,044
3 Kornfeld Eliyahu Peter 3,373
4 Maciej Los 2,581

Advertise | Privacy | Mobile
Web01 | 2.8.150327.1 | Last Updated 29 Aug 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100