Index Match Match in VBA

[CarolynS]

Hi

I've been trying to use index match match in vba and I just can't get it right. I've tried a couple of ways. The 1st thing I tried was:

Me.Controls("POinv" & x).Value = Application.Index(wsP.Range("B:L"), Application.Match(Me.Inv2.Value, wsP.Range("K:K"), 0), Application.Match(wsP.Cells(3, x + 1), wsP.Range("3:3"), 0))

I am getting an error saying "could not find specified object"

I then tried

       With Application
            Col = .Match(Me.Inv2.Value, wsP.Range("K:K"), 0)
            Rw = .Match(wsP.Cells(3, x + 1), wsP.Range("3:3"), 0)
            Me.Controls("POinv" & x).Value = .Index(wsP.Range("B:L"), Col, Row)
        End With

This is also giving the "could not find specified object" error and the error is occuring on the 4th line of code however, the problem seems to be the Col variable as this variable is shwoing a value of "error 2042". Rw variable seems to be correct.

What am I doing wrong?

Full code below for reference:

Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim WSi As Worksheet
Dim wsP As Worksheet
Dim Cellref As String
Dim x As Integer
Dim Rw As Variant
Dim Col As Variant

Set wb = Workbooks("Learning and Development Purchase Order and Invoice Tracker")
Set WSi = wb.Sheets("InvoiceLists")
Set wsP = wb.Sheets("PORequestLists")

'validate purchase order info on invoice form
 
'unlock text boxes
'checkn if PO number exists
    If WorksheetFunction.CountIf(wsP.Range("K:K"), Me.Inv2.Value) = 0 Then
        MsgBox "No corresponding purchase order found. Please try again!"
        Exit Sub
    End If
    
    For x = 1 To 11
    
        'Me.Controls("POinv" & x).Value = Application.Index(wsP.Range("B:L"), Application.Match(Me.Inv2.Value, wsP.Range("K:K"), 0), Application.Match(wsP.Cells(3, x + 1), wsP.Range("3:3"), 0))
        
       With Application
            Col = .Match(Me.Inv2.Value, wsP.Range("K:K"), 0)
            Rw = .Match(wsP.Cells(3, x + 1), wsP.Range("3:3"), 0)
            Me.Controls("POinv" & x).Value = .Index(wsP.Range("B:L"), Col, Row)
        End With
            
            
        Next

End Sub

 

[Jonmo1]

Try

Dim MyVar As Variant
On Error Resume Next
MyVar = CDbl(Me.Inv2.Value)
On Error GoTo 0
If IsEmpty(MyVar) Then MyVar = Me.Inv2.Value
With Application
    Col = .Match(wsP.Cells(3, x + 1), wsP.Range("3:3"), 0)
    Rw = .Match(MyVar, wsP.Range("K:K"), 0)
    Me.Controls("POinv" & x).Value = .Index(wsP.Range("B:L"), Rw, Col)
 End With
MyVar = Empty

 

[CarolynS]

That has solved the issue. Thank you

 

[Jonmo1]

You're welcome