PDA

View Full Version : Concatenating two ranges in MATCH function



clesio
01-04-2019, 05:45 AM
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
01-04-2019, 09:08 AM
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
01-04-2019, 09:19 PM
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
01-07-2019, 06:55 AM
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
01-07-2019, 09:33 AM
Did you try the macro in post #2?

clesio
01-07-2019, 09:51 AM
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
01-07-2019, 01:41 PM
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



23526





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