Hi guys,
I'm quite a newbie in VBA and I've been trying to create a userform for a function that Concatenates all the Index Match Values from a table of clients' names based on a single value.
Background:
1) All clients are assigned a Reference Number
2) If they're a family, they will have the same reference number,
thus the need for concatenate because a Ref. Number can be more than 1 name
Anyhow, I've been using this VBA to help me define the function:
Before I begin creating a userform, I have a cell in a table that helps me populate the client's names. But it is getting to the point that an excel table to too long (too many rows and too many columns and I want a cleaner view, resulting in an exploration of USERFORM.
Generally, in an Excel table, I will assign a cell with the formula:
E2 = The cell where I input the Ref. Number
Table_MIS[CASENAME] = The Ref. Number column in a Masterfile I point to
Table_MIS[CLIENTNAME] = The client's name column in the Masterfile
This will then show all the clients' names separated by comma based on the Ref Number I input at column E
So, as you see, it is mostly an index match function, but that it has been supported by a Module.
I wonder if it is possible to incorporate this in VBA, as I'm creating a USERFORM
The form has the followings in concern:
1) A textbox called "REF_Input"
2) A label called "ClientName_Label"
I would like that, if a user types in the textbox REF_Input, the label caption of "ClientName_Label" automatically uses the formula specified above to show the results of the client's name.
I understand the Excel formulas but I don't for VBA for index match, so I'm looking to see how it would be possible to do that. Any help is appreciated. Thank you.
I'm quite a newbie in VBA and I've been trying to create a userform for a function that Concatenates all the Index Match Values from a table of clients' names based on a single value.
Background:
1) All clients are assigned a Reference Number
2) If they're a family, they will have the same reference number,
thus the need for concatenate because a Ref. Number can be more than 1 name
Anyhow, I've been using this VBA to help me define the function:
Code:
Function LookUpConcat(ByVal SearchString As String, _
SearchRange As Range, _
ReturnRange As Range, _
Optional Delimiter As String = " ", _
Optional MatchWhole As Boolean = True, _
Optional UniqueOnly As Boolean = False, _
Optional MatchCase As Boolean = False)
Dim X As Long, CellVal As String, ReturnVal As String, Result As String
If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
(ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
LookUpConcat = CVErr(xlErrRef)
Else
If Not MatchCase Then SearchString = UCase(SearchString)
For X = 1 To SearchRange.Count
If MatchCase Then
CellVal = SearchRange(X).value
Else
CellVal = UCase(SearchRange(X).value)
End If
ReturnVal = ReturnRange(X).value
If MatchWhole And CellVal = SearchString Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
End If
Continue:
Next
LookUpConcat = Mid(Result, Len(Delimiter) + 1)
End If
End Function
Before I begin creating a userform, I have a cell in a table that helps me populate the client's names. But it is getting to the point that an excel table to too long (too many rows and too many columns and I want a cleaner view, resulting in an exploration of USERFORM.
Generally, in an Excel table, I will assign a cell with the formula:
Code:
=IF(E2="","",LookUpConcat(E2,Table_MIS[CASENAME],Table_MIS[CLIENTNAME], ", "))
E2 = The cell where I input the Ref. Number
Table_MIS[CASENAME] = The Ref. Number column in a Masterfile I point to
Table_MIS[CLIENTNAME] = The client's name column in the Masterfile
This will then show all the clients' names separated by comma based on the Ref Number I input at column E
So, as you see, it is mostly an index match function, but that it has been supported by a Module.
I wonder if it is possible to incorporate this in VBA, as I'm creating a USERFORM
The form has the followings in concern:
1) A textbox called "REF_Input"
2) A label called "ClientName_Label"
I would like that, if a user types in the textbox REF_Input, the label caption of "ClientName_Label" automatically uses the formula specified above to show the results of the client's name.
I understand the Excel formulas but I don't for VBA for index match, so I'm looking to see how it would be possible to do that. Any help is appreciated. Thank you.