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

 

[Roderick_E]

.Index is still going to require APPLICATION before, application.index since not a VBA function

 

[CarolynS]

Hi Roderick, thanks for your quick reply. I'm not sure I fully understand. It still seems to be an issue with the Col variable having an error before it even gets to the index line

 

[Roderick_E]

Hi there sorry yes the .match will also require application.match not just .match

 

[Jonmo1]

Hi there sorry yes the .match will also require application.match not just .match

That's what the 'With Application' structure is doing..

So the problem is here
Col = .Match(Me.Inv2.Value, wsP.Range("K:K"), 0)

It would seem that Me.Inv2.Value doesn't exist as an 'Exact' match within wsP.Range("K:K")

What type of values are those?

 

[CarolynS]

Ok so the Col issue was me being stupid. I've set the variable to integer as me.inv2.value is always a number. No idea why I was asking it to store as a string. So my variables are working fine now but there is still the issue with the 4th line:

Me.Controls("POinv" & x).Value = .Index(wsP.Range("B:L"), Col, Row)

This seems to go back to your original comment about appiclation.index but I'm not sure what I need to change this line to?

Thanks

 

[Jonmo1]

I would guess that they are numbers.

The problem is that textboxes (listboxes/comboboxes whatever Inv2 is) actually contain TEXT, not numbers.
Countif Sees it because it will consider 'Numbers Stored as Text' the same as real numbers, but MATCH will not.

Try
Col = .Match(CDbl(Me.Inv2.Value), wsP.Range("K:K"), 0)

 

[CarolynS]

Sorry I'm just not with it today What I meant to say above was i changed my code to set me.Inv2.value as a variable and then used that variable within the col = line of code. I accidentally set that variable to string and still got error but changed to integer and it works ok now and the col variable is shwoing the correct value. Still getting the same error on the index line though. yes the with block has already stated the application part. Not sure where to go from here?

My amended code is below:

Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim WSi As Worksheet
Dim wsP As Worksheet
Dim Val As Integer
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
    
    Val = Me.Inv2.Value
    
    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(Val, 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

 

[CarolynS]

I would guess that they are numbers.

The problem is that textboxes (listboxes/comboboxes whatever Inv2 is) actually contain TEXT, not numbers.
Countif Sees it because it will consider 'Numbers Stored as Text' the same as real numbers, but MATCH will not.

Try
Col = .Match(CDbl(Me.Inv2.Value), wsP.Range("K:K"), 0)

sorry cross post. I will try that now

 

[CarolynS]

sorry cross post. I will try that now

Just tried it and it the Col variable is looking fine but still same error on the idex line - "could not find specified object"

I'm wondering if my col and rw variables need to be integers not variants?