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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.