Concatenating two ranges in MATCH function

[clesio]

Greetings to all! This is my first post!

I've been trying to handle MATCH function, concatenating two ranges for multiple columns search and it keeps telling me Error "13".

Here is the code line where it stops:

UserForm1.TextBox2.Text = Application.Index(Sheets("Plan1").Range("A1:E73"), Application.Match(UserForm1.ComboBox1.Text & Month(Date), Sheets("Plan1").Range("A:A") & Sheets("Plan1").Range("B:B")), 4)

Note that this comand works as a cell formula in the "Match Example" workbook when i use ctrl+shift+enter, but VBA is unable to handle that using the same structure.

Can someone help?

[Paul_Hossler]

Workaround, and I'd change the logic a little bit

Option Explicit


Dim aryTemp() As String
Dim rData As Range

Private Sub ComboBox1_Change()
    Dim m As Long
    
    With Me
        If Len(.ComboBox1.Text) = 0 Or Len(.TextBox1.Text) = 0 Then Exit Sub
        
        m = Application.WorksheetFunction.Match(.ComboBox1.Text & "#" & .TextBox1.Text, aryTemp, 0)
    
        Application.EnableEvents = False
        .TextBox2.Text = rData.Cells(m, 4)
        Application.EnableEvents = True
    End With
End Sub


Private Sub CommandButton1_Click()
    Unload Me
End Sub


Private Sub TextBox1_Change()
    Dim m As Long
    
    With Me
        
        If Len(.ComboBox1.Text) = 0 Or Len(.TextBox1.Text) = 0 Then Exit Sub
    
        m = Application.WorksheetFunction.Match(.ComboBox1.Text & "#" & .TextBox1.Text, aryTemp, 0)
    
        Application.EnableEvents = False
        .TextBox2.Text = rData.Cells(m, 4)
        Application.EnableEvents = True
    End With
End Sub

Private Sub UserForm_Initialize()
    Dim n As Long
    
    Set rData = Sheets("Plan1").Cells(1, 1).CurrentRegion
    
    ReDim aryTemp(1 To rData.Rows.Count)
    
    For n = LBound(aryTemp) To UBound(aryTemp)
        aryTemp(n) = rData.Cells(n, 1) & "#" & rData.Cells(n, 2)
    Next n
    Application.EnableEvents = False
    UserForm1.TextBox1.Text = Month(Date)
    Application.EnableEvents = True

End Sub

[mana]

Private Sub ComboBox1_Change()
    Dim f As String
    
    TextBox1.Text = Month(Date)
    f = "INDEX(Plan1!A1:E73,MATCH(""" & ComboBox1.Text & Month(Date) & """,Plan1!A:A&Plan1!B:B,0),4)"
    TextBox2.Text = Evaluate(f)
    
End Sub

[clesio]

Private Sub ComboBox1_Change()
    Dim f As String
    
    TextBox1.Text = Month(Date)
    f = "INDEX(Plan1!A1:E73,MATCH(""" & ComboBox1.Text & Month(Date) & """,Plan1!A:A&Plan1!B:B,0),4)"
    TextBox2.Text = Evaluate(f)
    
End Sub

Same error using Evaluate method.

[Paul_Hossler]

Did you try the macro in post #2?

[clesio]

Did you try the macro in post #2?

Yes, it works. But I was trying to use a simpler logic. In mu original workbook (a lot more complex), I would have to repeat this routine for a large number o fields to fulfill lots of forms. Anyway, your solution works and it made me learn a new logic to use in other ocasions.

Thanks!

PS: I decided to create an auxiliar tab to feed the forms. In the worksheet I can use the MATCH method and other people who work with me can understand the code easily if they need to change that when i'm out (It has to be understandable to someone with less knowlege).

The MATCH method seems to be incompatible to anything but string (including range) when using VBA, it's a pity.

[Paul_Hossler]

Yes, it works. But I was trying to use a simpler logic. In my original workbook (a lot more complex), I would have to repeat this routine for a large number o fields to fulfill lots of forms. Anyway, your solution works and it made me learn a new logic to use in other occasions.

Thanks!

PS: I decided to create an auxiliar tab to feed the forms. In the worksheet I can use the MATCH method and other people who work with me can understand the code easily if they need to change that when I'm out (It has to be understandable to someone with less knowledge).

If the other fields are just additional instances of the 3, you can modularize the logic. Look at the last 2 subs below.

Making it understandable to someone else is very important, or even yourself 6 months from now when you have to go back and look at it

You can make this even more general purpose (but more complex) by using Classes. I did a very complex application before I retired where using Class modules and Interface modules would have really helped, but I knew I had to turn it over to someone else so I kept their learning curve as simple as possible



Capture.JPG

Option Explicit

Dim aryTemp() As String
Dim rData As Range

Private Sub ComboBox1_Change()
    Call pvtComboBox_Change(Me.ComboBox1, Me.TextBox1A, Me.TextBox1B)
End Sub

Private Sub TextBox1A_Change()
    Call pvtComboBox_Change(Me.ComboBox1, Me.TextBox1A, Me.TextBox1B)
End Sub

Private Sub ComboBox2_Change()
    Call pvtComboBox_Change(Me.ComboBox2, Me.TextBox2A, Me.TextBox2B)
End Sub

Private Sub TextBox2A_Change()
    Call pvtComboBox_Change(Me.ComboBox2, Me.TextBox2A, Me.TextBox2B)
End Sub

'===========================================================================================================
Private Sub UserForm_Initialize()
    Dim n As Long
    
    Set rData = Sheets("Plan1").Cells(1, 1).CurrentRegion
    
    ReDim aryTemp(1 To rData.Rows.Count)
    
    For n = LBound(aryTemp) To UBound(aryTemp)
        aryTemp(n) = rData.Cells(n, 1) & "#" & rData.Cells(n, 2)
    Next n
    Application.EnableEvents = False
    UserForm1.TextBox1A.Text = Month(Date)
    UserForm1.TextBox2A.Text = Month(Date)
    Application.EnableEvents = True

End Sub


'===========================================================================================================
'===========================================================================================================
Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub pvtComboBox_Change(CB As MSForms.ComboBox, TBA As MSForms.TextBox, TBB As MSForms.TextBox)
    Dim m As Long
    
    If Len(CB.Text) = 0 Or Len(TBA.Text) = 0 Then Exit Sub
        
    m = Application.WorksheetFunction.Match(CB.Text & "#" & TBA.Text, aryTemp, 0)
    
    Application.EnableEvents = False
    TBB.Text = rData.Cells(m, 4)
    Application.EnableEvents = True
End Sub

Private Sub pvtTextBox_Change(CB As MSForms.ComboBox, TBA As MSForms.TextBox, TBB As MSForms.TextBox)
    Dim m As Long
    
    If Len(CB.Text) = 0 Or Len(TBA.Text) = 0 Then Exit Sub
    
    m = Application.WorksheetFunction.Match(CB.Text & "#" & TBA.Text, aryTemp, 0)
    
    Application.EnableEvents = False
    TBB.Text = rData.Cells(m, 4)
    Application.EnableEvents = True
End Sub