Excel VBA loads data to Excel sheet in wrong location

Question

4.00/5 (1 vote)

See more:

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.
__________________________________________________

VB

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

__________________________________________________

VB

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 7:36am

tv1owner

Updated 29-Aug-12 8:04am

luisnike19

v2

Add a Solution

Comments

Kenneth Haugland 29-Aug-12 14:30pm

WHy did you expect one to be faster than the other?

tv1owner 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2012-08-29T09:10:00

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:

VB

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:

VB

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.