Bypass 255 character limit of Index MATCH Function with VBA-CodePudding

My reference is a table named "TBQA" with column headers "Question" and "Answer". I have used the "Answer" column as a RowSource in a UserForm ComboBox drop-down. I am trying to use the selection in this UserForm drop-down in an Index Match function via VBA. Unfortunately, some of my selections in the drop-down surpass the 255 character limit.

I have successfully bypassed the 255 character limit for the MATCH function in the worksheet, however, I cannot replicate it using VBA. The worksheet function looks like this:

=INDEX(TBQA[Question],MATCH(TRUE,INDEX(TBQA[Answer]=TBQA[@Answer],0),0))

Here is my code:

MsgBox Application.WorksheetFunction.Index(ws1.Range("TBQA[Question]"), _
Application.WorksheetFunction.Match(True, _
Application.WorksheetFunction.Index(TBABox.Value = ws1.Range("TBQA[Answer]"), 0), 0))

I am receiving the Type Mismatch error code. It would seem that it is a simple correction. I am guessing that if I can get it to work in the worksheet I can get VBA to do the same. PLEASE HELP!!! Thank you GREATLY in advance.

CodePudding user response：

If you have the latest version of Excel, you can use the XMATCH function. It doesn't have the 255 character limit.

With Application
    MsgBox .Index(ws1.Range("TBQA[Question]"), .XMatch(TBABox.Value, ws1.Range("TBQA[Answer]")))
End With

Otherwise, try the following formula instead...

With Application
    MsgBox .Index(ws1.Range("TBQA[Question]"), Evaluate("MATCH(TRUE," & ws1.Range("TBQA[Answer]").Address(, , , True) & "=" & TBABox.Value & ",0)"))
End With

However, if your combobox contains a text value instead of a number, you'll need to enclose its value within quotes...

With Application
    MsgBox .Index(ws1.Range("TBQA[Question]"), Evaluate("MATCH(TRUE," & ws1.Range("TBQA[Answer]").Address(, , , True) & "=""" & TBABox.Value & """,0)"))
End With

CodePudding user response：

I have figured it out!!! I created a new table column titled "AbbAns" to represent the abbreviated answers. In this column I used the formula:

=LEFT([@Answer],255)

Then, instead of referencing the "Answer" column in the code I just reference the "AbbAns" column. The final code that works is this:

Private Sub ShowMeQues_Click()

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("Information")
Set ws2 = Sheets("Resource")
   
Set Question = Application.WorksheetFunction.Index(ws1.Range("TBQA[Question]"), _
Application.WorksheetFunction.Match(Left(TBABox.Value, 255), ws1.Range("TBQA[AbbAns]"), 0))

MsgBox Question

End Sub

This may not be the easiest way to do it, but IT WORKS!!!