excel VBA数组：生成错误的输出

frebpwbc 于 2023-01-03 发布在其他

关注(0)|答案(1)|浏览(176)

我是一名VBA Excel初学者，学习过二维数组，我只是想从Excel中的一个标记表中捕捉一些值到一个数组中，并在下一个表中返回这些值，我尝试将数组设置为动态的，我编写了如下代码：

Private Sub CommandButton1_Click()

Dim myarr() As String

Dim lrow As Integer, lc As Integer, r As Integer, c As Integer
lrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lc = Sheets("Sheet1").Range("A1").End(xlToRight).Column

ReDim myarr(lrow, lc) As String
For r = 1 To lrow
    For c = 1 To lc
        myarr(r, c) = Cells(r, c).Value
    Next c
Next r

Sheets("Sheet2").Select

For r = 1 To lrow
    For c = 1 To lc
        Cells(r, c) = myarr(r, c)
        
        
    Next c
Next r

End Sub

我期望数组返回工作表2中的值，但运行代码后没有结果。但它没有发出错误。请帮助我解决这个问题，以便我能够正确学习VBA Excel。提前感谢。

excel

来源：https://stackoverflow.com/questions/74981783/vba-array-generating-wrong-output

1条答案

按热度按时间

cygmwpex1#

在可能的情况下，使用特定的工作表引用来限定范围，不要依赖于Select。我希望在运行代码时选择工作表2，以便第一个Cells(r, c).Value将引用该工作表。

Option Explicit
Private Sub CommandButton1_Click()

    Dim ws1 As Worksheet, ws2 As Worksheet, wb As Workbook
    Dim myarr() As String
    Dim lrow As Integer, lc As Integer, r As Integer, c As Integer
    
    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets("Sheet1")
    Set ws2 = wb.Sheets("Sheet2")
    
    lrow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    lc = ws1.Range("A1").End(xlToRight).Column
    
    ReDim myarr(lrow, lc) As String
    For r = 1 To lrow
        For c = 1 To lc
            myarr(r, c) = ws1.Cells(r, c).Value
        Next c
    Next r
    
    For r = 1 To lrow
        For c = 1 To lc
            ws2.Cells(r, c) = myarr(r, c)
        Next c
    Next r

End Sub

或者更简单地说

Private Sub CommandButton1_Click1()

    Dim myarr
    With ThisWorkbook
       myarr = .Sheets("Sheet1").UsedRange
       .Sheets("Sheet2").Range("A1").Resize(UBound(myarr), UBound(myarr, 2)) = myarr
    End With

End Sub

赞(0）回复(0）举报 2023-01-03

我来回答

excel VBA数组：生成错误的输出

1条答案

相关问题

热门标签

最新问答