excel - 如何用函数输出替换范围，并覆盖范围值？

Question

我有一个看起来像这样的 vba 代码，试图用函数 FuzzyVLookup 的输出替换一个单元格，目前，我可以读取单元格 mAr(i,1) 并有一个 FuzzyVLookup(mAr(i,1) 的输出， inRng1 ,1) 但我无法覆盖最初的 mAr(i,1)

提前感谢您的帮助

Public Sub Test()

Dim StartTime As Double
Dim MinutesElapsed As String

'Remember time when macro starts
StartTime = Timer
    '---------------------------------------------------------------
    '-- Select Range A for comparison to be compared with Range B --
    '---------------------------------------------------------------
    Const LBLS  As String = "Names to be matched "
    Const xNAME As String = "Fuzzy Match Lead"

    Set inRng = Application.Selection
    Set inRng = Application.InputBox(LBLS, xNAME, inRng.Address, Type:=8)

    '-----------------------------------------------
    '-- Select Range B for Range A to be compared --
    '-----------------------------------------------
    Const LBLS1  As String = "Names to be matched to "
    Const xNAME1 As String = "Fuzzy Match Source"

    Set inRng1 = Application.Selection
    Set inRng1 = Application.InputBox(LBLS1, xNAME1, inRng.Address, Type:=8)

    If inRng.Columns.Count > 1 Or inRng.Areas.Count > 1 Then
        MsgBox "Please select a single (continuous) column"
        Exit Sub
    End If

    allRows = inRng.Rows.Count
    If inRng.Count = 1 Then     'if only one cell selected force mAr to array
        ReDim mAr(1, 1)
        mAr(1, 1) = inRng.Value2
    Else
        mAr = inRng.Value2
    End If

    For i = 1 To allRows
        mAr(i, 1) = FuzzyVLookup(mAr(i, 1), inRng1, 1)
    Next

    inRng = mAr                 'place memory array back to range

'Determine how many seconds code took to run
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

'Notify user in seconds
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation


End Sub

标签： excelvba