arrays - 使用数组将文本从一个工作表传递到另一个工作表

Question

我正在尝试根据标准 (*) 将数据从工作表 3 传递到工作表 4。如果结果是数字，但结果是文本，程序就会失败。当我用文本代替数字时，如何克服这种情况。

Public Sub TestArray3()
    'Array to copy data from Sheet3 to Sheet4 Based on criterion "in this case*"
    Dim tempVar As Integer, anotherIteration As Boolean, i As Integer
    Dim J As Integer, ArraySize As Integer, myArray() As Integer
    Dim newArray() As Integer, FinalRow As Integer, linha As Integer
    Dim counter As Integer, cel1 As Range

    Sheets("Folha3").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row ' Find the last row of data
    ArraySize = FinalRow 'Get Array Size
    ReDim myArray(ArraySize - 1)
    For linha = 1 To FinalRow
        Set cel1 = Cells(linha, 1)
        If cel1 = "*" Then
            myArray(linha - 1) = Val(Cells(linha, "B").Value) 'Populate de Array
        End If
    Next linha

    ReDim newArray(LBound(myArray) To UBound(myArray)) 'Avoid zeros in Array
    For i = LBound(myArray) To UBound(myArray)
        If myArray(i) <> "0" Then
            J = J + 1
            newArray(J) = myArray(i)
        End If
    Next i

    ReDim Preserve newArray(LBound(myArray) To J)
    ArraySize = J
    Sheets("Folha4").Select 'Write data to Sheet 4 column A
    Range("A1").Resize(J - LBound(newArray) + 1)=Application.Transpose(newArray)
End Sub

Answer 1

我不清楚您实际上想要从/粘贴到哪里，但这是在工作表之间移动数据的[几种]方法之一，包括带转置和不转置的方法

希望这个示例能够阐明这些步骤:

Sub copyRangeToOtherSheet()

    Dim lastRow As Long, lastCol As Long, rgSrc As Range, rgDest As Range, arr() As Variant

    With ThisWorkbook.Sheets("Sheet1")                               'set source worksheet

        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row              'find last row of Col A
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column      'find last col of Row 1
        Set rgSrc = Range(.Range("A1"), .Cells(lastRow, lastCol))    'create range (from A1)

    End With

    arr = rgSrc                                                      'dump range into array

    With ThisWorkbook.Sheets("Sheet2")                               'set destination sheet

      'OPTION #1: Populate destination in "original" orientation
        Set rgDest = .Range("A1")                           'set destination top-left corner
        Set rgDest = rgDest.Resize(UBound(arr, 1), UBound(arr, 2))  'fit to array rows/col's
        rgDest = arr                                          'dump array to worksheet range

      'OPTION #2: Populate destination in "transposed" orientation
        Set rgDest = .Range("D1")                           'set destination top-left corner
        Set rgDest = rgDest.Resize(UBound(arr, 2), UBound(arr, 1))  'fit to array col's/rows
        rgDest = WorksheetFunction.Transpose(arr)  'dump transposed array to worksheet range

    End With

End Sub

请注意，如果您不提前设置数组的大小，则最简单 - 只要数组尚未指定尺寸，Excel 就会自动调整其大小(这就是为什么它仅声明为 arr()作为变体)。

在目标端，我们可以选择一个单元格作为范围的左上角，然后 ReSize基于数组上限的范围 ( UBound )。

如果我们要 Transpose单元格，我们必须交换目标范围中的行/列数。

<小时/>

更多信息:

• 我发现一个非常有用的资源是 Chip Pearson 的 VBA Arrays And Worksheet Ranges 。