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

 

[CarolynS]

Oh dear, I must really be having a bad day - The code is currently on x=1 so it's trying to fill a text box called POinv1 and it seems that I named all my other textboxes except for POinv1 I'm guessing that's why it couldn't find the object!

 

[CarolynS]

I have now named my text box correctly (have also switched around Rw and Col in the index line as they seemed to be the wrong way round. Still getting an error though, again on the Me.Controls("POinv" & x).Value = .Index(wsP.Range("B:L"), Row, Col) line. error is "Could not set the property value. Type mismatch"

Full amended code 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)
            Col = .Match(CDbl(Me.Inv2.Value), wsP.Range("K:K"), 0)
            Me.Controls("POinv" & x).Value = .Index(wsP.Range("B:L"), Row, Col)
        End With
           
            
        Next

End Sub

 

[Jonmo1]

You have your variable misspelled..

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

Adding this line to the top of your module will help discover these types of errors

Option Explicit

 

[Jonmo1]

They're also bakcwards...

You have the rw variable finding a value across 3:3, and Col finding a value in K:K
I would think that should be

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

 

[Jonmo1]

Also, I think you'll find that returns a value from the wrong column (1 to the right)

You're match is using 3:3 as the range.
So say the match is found in G3 for example, then the result of match is 7
But you're indexing B:L
The 7th column of B:L is H
So the Index would result in a value from column H, not G.

You should make both those ranges use the same columns.

Change 3:3 to B3:L3

 

[CarolynS]

Thank Jonmo1. Yes I had forgotton that I was only looking from column B onwards. I have changedthe range to B3:L3. Also switched round the rw and column and changed Row to rw. Everything seems to be working fine now

Out of interest what idoes the CDbl in the below code do? I've never come across it before

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

Thank you for all your help

 

[Jonmo1]

Like I said the Textbox (or whatever Inv2 is) contains a TEXT string, not a number.

CDbl just converts the text number into a real number.
It serves the same purpos of how you previouls assigned the INV2 to a variable, and dimmed that variable as Integer.

 

[CarolynS]

That's great. Thank you

 

[Jonmo1]

You're welcome.

 

[CarolynS]

I'vw just been told the invoice numbers that will be used in this form couod be numbers or numbers and letters. If I'm using CDbl and the invoice number contains letters it errors. If it take out CDbl it works fine if the invoice number contains letters but if it's an invoice number with all numbers it errors. How do I set it up to accept numbers and letters? It is the folllowing code that is erroring:

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