# VBA VLOOKUP WITH MATCH NOT WORKING



## ramanaj (Dec 26, 2022)

Sub InRev_DDQ()

Dim Sh1_Last_Row As Long
Dim ACDCT As Workbook

Set tbook = ThisWorkbook

Application.EnableEvents = False
Application.DisplayAlerts = False

On Error GoTo Ramana
Sh1_Last_Row = tbook.Worksheets("DATA").Range("A" & Rows.Count).End(xlUp).Row - 7

Set ACDCT = Workbooks.Open(tbook.Worksheets("Master").Range("S5").Value, UpdateLinks = False)
Set val2d_range = ACDCT.Worksheets("I-2d.Valuation").Range("B:CO")

tbook.Activate

For I = 7 To Sh1_Last_Row
With tbook.Worksheets("DATA")
         .Range("J" & I).Value = Application.WorksheetFunction.VLookup(Worksheets("Data").Range("A" & I), _
            val2d_range, WorksheetFunction.Match(Worksheets("Master").Range("S9"), val2d_range("5:5"), 0), False)

       End With

Next I

Ramana:
'Range("J" & I).Value = "Not Found"
'Range("K" & I).Value = "Not Found"
Resume Next

End Sub


----------



## Alex Blakenburg (Dec 27, 2022)

Give this a try:


```
For I = 7 To Sh1_Last_Row
    With Application
        tbook.Worksheets("DATA").Range("J" & I).Value = .IfError(.VLookup(Worksheets("Data").Range("A" & I), _
                    val2d_range, .Match(Worksheets("Master").Range("S9"), val2d_range.*Rows*("5:5"), 0), False), "Not Found")
    End With
Next I
```


----------



## ramanaj (Dec 27, 2022)

Hi Rich, Thanku for your support and when running macro showing below error






Alex Blakenburg said:


> Give this a try:
> 
> 
> ```
> ...


----------



## Alex Blakenburg (Dec 27, 2022)

What line of code is highlighted when you get that error ?


----------



## ramanaj (Dec 27, 2022)

tbook.Worksheets("DATA").Range("J" & I).Value = .IfError(.VLookup(Worksheets("Data").Range("A" & I), _
                    val2d_range, .Match(Worksheets("Master").Range("S9"), val2d_range.*Rows*("5:5"), 0), False), "Not Found")


Alex Blakenburg said:


> What line of code is highlighted when you get that error ?


----------



## ramanaj (Dec 27, 2022)

tbook.Worksheets("DATA").Range("J" & I).Value = .IfError(.VLookup(Worksheets("Data").Range("A" & I), _
                    val2d_range, .Match(Worksheets("Master").Range("S9"), val2d_range.*Rows*("5:5"), 0), False), "Not Found")


----------



## ramanaj (Dec 27, 2022)

tbook.Worksheets("DATA").Range("J" & I).Value = .IfError(.VLookup(Worksheets("Data").Range("A" & I), _
                    val2d_range, .Match(Worksheets("Master").Range("S9"), val2d_range.*Rows*("5:5"), 0), False), "Not Found")


----------



## Alex Blakenburg (Dec 27, 2022)

Please use the button marked VBA and post your entire code.


----------

