Vlookup with Match and Not Match return with specific values in VBA. Please find the below code. I n-CodePudding

Vlookup with Match and Not Match return with specific values in VBA. Please find the below code. I need to know the how to get the match value:

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

CodePudding user response：

Please, create a new variable:

Dim mtch

Comment On Error GoTo K code line.

Delete all the code starting from K: up to End Sub (exclusive).

Then, replace:

 goalasRK.Range("AK" & X).Value = Application.WorksheetFunction.Vlookup(goalasRK.Range("H"&_      
         X).Value, DataRng, 1, False)

with:

   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