Vlookup with Match 和 Not Match 在 VBA 中回傳特定值。請找到以下代碼。我需要知道如何獲得匹配值：
Sub Vlookup()
Dim goalasRK As Worksheet, dataRK As Worksheet
Dim goalasLastRow As Long, dataLastRow As Long, X As Long
Dim DataRng As Range
Set goalasRK = ThisWorkbook.Worksheets("AddNewData")
Set dataRK = ThisWorkbook.Worksheets("Open")
goalasLastRow = goalasRK.Range("B8:B" & Rows.Count).End(xlDown).Row
dataLastRow = dataRK.Range("A" & Rows.Count).End(xlUp).Row
Set DataRng = dataRK.Range("B2:B" & dataLastRow)
For X = 8 To goalasLastRow
On Error GoTo K
goalasRK.Range("AK" & X).Value = Application.WorksheetFunction.Vlookup(goalasRK.Range("H"&_
X).Value, DataRng, 1, False)
(I need to Update the Match rows as "Match") "Any one help me on this"
Next X
K:
If Err.Number = 1004 Then
goalasRK.Range("AK" & X).Value = "Not Found"
goalasRK.Range("AK" & X).Interior.Color = vbRed
Resume Next
Else
End If
End Sub
uj5u.com熱心網友回復：
請創建一個新變數：
Dim mtch
注釋On Error GoTo K代碼行。
洗掉從 K:up 到End Sub(exclusive) 的所有代碼。
然后，替換：
goalasRK.Range("AK" & X).Value = Application.WorksheetFunction.Vlookup(goalasRK.Range("H"&_
X).Value, DataRng, 1, False)
和：
mtch = Application.VLookup(goalasRK.Range("H" & x).Value, DataRng, 1, False)
If Not IsError(mtch) Then
goalasRK.Range("AK" & x).Value = mtch
Else
With goalasRK.Range("AK" & x)
.Value = "Not Found"
.Interior.color = vbRed
End With
End If
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/328275.html
上一篇：VBA代碼匯總每個客戶商品的價格