To see the value selected (excel vba code)

Question

I'm new to Excel VBA and I'm trying to write code to make Excel flash up the row where the entry is. The entry I'm working on has two entries in a cell ("vbnewline"). Here's is my code and the error I keep getting is

"run-time error '1004' method match of object 'worksheetfunction' failed":

Will appreciate if there's someone who wouldn't mind helping.

I want a code formula that will tell excel to flash up the targeted row whenever i select an entry and click continue, that is my question basically.

The SpreadSheet

Combobox that's populated by a dynamic range

Main Userform code

Edit userform code that im stuck on

What is the value of ColumnC_Menu,? and Range("Dyn_Business_Name_Website")? What should be the right answer? — Foxfire And Burns And Burns
columnC_Menu is a combobox which is populated by the dynamic range entry of a cell and the entries of those values are joined by vbnewline. — Bill
If you hard code the value from the combo box into the formula does it return the expected value? Try to narrow down which part is generating the problem. It may well be to do with the new line. An example input expected output in this situation would be very helpful as mentioned by another. — QHarr
We're going to need more information than that in order to help you. Please edit your question so that it clearly identifies the data involved. The error you're getting means WorksheetFunction.Match did not find an exact match for ColumnC_Menu within Sheets("Data").Range("Dyn_Business_Name_Website"). If you wrote an Excel formula that did exactly the same thing, that formula would evaluate to an error, too. Feel free to include a screenshot of the data, if it helps. Do I understand correctly that ColumnC_Menu does not have the vbNewLine characters but the lookup range does? — Mathieu Guindon
Also the code currently assumes the match does find what it's looking for. Adding proper error handling, or using the late-bound Application.Match function (which requires capturing the returned value in a Variant, and validating the returned value with IsError), would allow you to handle the situation where the match fails. — Mathieu Guindon

Casey Casey · Accepted Answer · 2018-07-31T05:56:00

I'm having a similar problem. Since the OP still doesn't seem to have an answer that satisfies, I thought I'd add my situation in here, also. I hope this isn't a faux pas!

I have a UserForm with a ComboBox that will offer drop down selection. When the User selects an item in the ComboBox and hits "Continue", I'm looking to call the data associated with that record for use in the next UserForm. I'm watching a video on how to do this, and his code is identical to mine, and runs fine. I'm on mac and he's on PC. Screen Grab of functioning code from YouTube video

Here's what I have:

Private Sub ContinueButton_Click()

Dim TargetRow As Integer    'variable for output

'Define variable using excel formula "Match"(ComboButton_name, Sheets("sheet with data on it").Range
'("Named dynamic range"), 0)

TargetRow = Application.WorksheetFunction.Match(Combo_Task_Select, Sheets("Tasks").Range("Dyn_AllTasks"), 0)

MsgBox TargetRow    'display result for verification of code function

End Sub`

To see the value selected (excel vba code)

1 Answers