Excel VBA在错误的位置将数据加载到Excel工作表 [英] Excel VBA loads data to Excel sheet in wrong location

问题描述

我编写了模块"Table2Excel2()"来代替"Table2Excel1()"，以提高运行速度.

然而，由模块"Table2Excel2()"加载到Excel工作表的数据与"Table2Excel1()"的数据相同，但是从多行开始.列(单元格P4，但预计为O3).我使用代码Debug.Print来确保LTable& MTable是相同的.请指教.非常感谢.
__________________________________________________

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

推荐答案

首先，阅读有关MS Excel对象的信息 [ ^ ]，因为工作表对象和工作表对象不同.

最佳编程实践是将代码与上下文一起使用.要解释我的意思，请执行以下操作:
1)打开或创建一些MS Excel工作簿，
2)激活第一个工作簿并运行以下代码:

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)然后激活另一个工作簿并运行上面的代码
4)等等...

您可以期待什么样的结果?
每个工作簿的每个第一个工作表在A列中填充为1到30的值.

要正确地将数据加载到正确的工作表中，您需要执行以下操作:

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.

这篇关于Excel VBA在错误的位置将数据加载到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！