vba - 循环遍历行和列时的索引/匹配

Question

我已经编写了以下代码，我希望用它来查找从第 21 列到另一张表中最后一行的值，并根据这张表中 A 列和另一张表中 B 列中的值将它们返回到这张表床单。

当我使用下面的代码时，我得到一个工作表错误。你能告诉我为什么吗？

Dim wsMvOld As Worksheet
Dim wsMvFile As Worksheet
Dim wsColumn As Long
Dim lastColumn As Long
Dim y As Integer
Dim i As Integer
Dim FrRngCount As Range

Set wsMvOld = wbMVRVFile.Worksheets(2)

wbMVRVFile.Worksheets.Add().Name = "MV " & Format(DateSerial(Year(Date), Month(Date), 0), "dd-mm-yy")

Set wsMvFile = wbMVRVFile.ActiveSheet

Set FrRngCount = wsMvFile.Range("A:A")
y = Application.WorksheetFunction.CountA(FrRngCount)

lastColumn = wsMvFile.Cells(1, wsMvFile.Columns.Count).End(xlToLeft).Column

For wsColumn = 21 To lastColumn
For i = 2 To y
    wsMvFile.Columns(wsColumn).Cells(i) =     Application.Index(wsMvOld.Range(wsColumn), Application.Match(wsMvFile.Range("A" & i), wsMvOld.Range("B:B"), 0))
Next i

Next wsColumn

End Sub

谢谢你的帮助!

Answer 1

这未经测试，但它将工作表函数替换为 vba 查找方法。

Private Sub Worksheet_Activate()
Dim wsMvOld As Worksheet
Dim wsMvFile As Worksheet
Dim wsColumn As Long
Dim lastColumn As Long
Dim y As Integer
Dim i As Integer
Dim FrRngCount As Range

Set wsMvOld = wbMVRVFile.Worksheets(2)

Set wsMvFile = wbMVRVFile.Worksheets.Add()
wsMvFile.Name = "MV " & Format(DateSerial(Year(Date), Month(Date), 0), "dd-mm-yy")



Set FrRngCount = wsMvFile.Range("A:A")
y = Application.WorksheetFunction.CountA(FrRngCount)

lastColumn = wsMvFile.Cells(1, wsMvFile.Columns.Count).End(xlToLeft).Column


For i = 2 To y
    Dim rng As Range
    Set rng = sMvOld.Range("B:B").Find(wsMvFile.Range("A" & i))
    For wsColumn = 21 To lastColumn
        If Not rng Is Nothing Then
            wsMvFile.Columns(wsColumn).Cells(i).Value = wsMvOld.Cells(rng.Row, wsColumn)
        Else
            wsMvFile.Columns(wsColumn).Cells(i).Value = 0
        End If
    Next wsColumn
Next i


End Sub