Match all values in one column based on combobox selection and find a value in second column based on combobox value.

[Mike Neal]

I have a combobox that contains Equip IDs and one that contains Task IDs. I would like to search column A for the Equip ID which there may be several instances of and with a second combobox select a Task ID in Column B that is in the same row as the Equip ID. After finding the matched pair I would like to use a command button to put today's date in the next empty cell in that row. This will be an ongoing record so I need to look for the next empty column each time the task is completed. This macro also needs to find the same matched pair in a different sheet with Column A being the Equip ID again and Column B being the Task ID and delete the entire row from the second sheet. I have been looking at making arrays to do this but nothing I've tried comes close to working. Any help would be appreciated. Thank you.

 

[Yongle]

The method below works but probably need to amend these 3 lines
Set ws = Sheets("Sheet1")
cb1 = ComboBox1.Text
cb2 = ComboBox2.Text
If you need help, then I need to know where the comboboxes are located (worksheet or userform) and, if on worksheet, whether they are Form Controls or Active-X objects

    Dim ws As Worksheet, matchFormula As String, cb1 As String, cb2 As String
    Set ws = Sheets("Sheet1")
    cb1 = ComboBox1.Text
    cb2 = ComboBox2.Text
    matchFormula = "match(" & Chr(34) & cb1 & cb2 & Chr(34) & ",A:A&B:B,0)"

    On Error Resume Next
    ws.Cells(Evaluate(matchFormula), ws.Columns.Count).End(xlToLeft).Offset(, 1) = Date
    On Error GoTo 0

After getting it to work, adapt to also delete the same pair from the 2nd sheet

 

[Mike Neal]

The method below works but probably need to amend these 3 lines
Set ws = Sheets("Sheet1")
cb1 = ComboBox1.Text
cb2 = ComboBox2.Text
If you need help, then I need to know where the comboboxes are located (worksheet or userform) and, if on worksheet, whether they are Form Controls or Active-X objects

    Dim ws As Worksheet, matchFormula As String, cb1 As String, cb2 As String
    Set ws = Sheets("Sheet1")
    cb1 = ComboBox1.Text
    cb2 = ComboBox2.Text
    matchFormula = "match(" & Chr(34) & cb1 & cb2 & Chr(34) & ",A:A&B:B,0)"

    On Error Resume Next
    ws.Cells(Evaluate(matchFormula), ws.Columns.Count).End(xlToLeft).Offset(, 1) = Date
    On Error GoTo 0

After getting it to work, adapt to also delete the same pair from the 2nd sheet

That worked great for finding the match and adding the date. I am not sure how to adapt it to delete and entire row on the other sheet though. I am using VBA userform comboboxes. My second sheet is "Sheet1" the data is in the same locations: column A = "Equip ID" and column B = "Task ID" as the first sheet. The comboboxs are named "TASKS_EQUIP_ID_LIST" and "TASKS_TASK_DUE_LIST". Thanks again for the help.

 

[Mike Neal]

That worked great for finding the match and adding the date. I am not sure how to adapt it to delete and entire row on the other sheet though. I am using VBA userform comboboxes. My second sheet is "Sheet1" the data is in the same locations: column A = "Equip ID" and column B = "Task ID" as the first sheet. The comboboxs are named "TASKS_EQUIP_ID_LIST" and "TASKS_TASK_DUE_LIST". Thanks again for the help.

Dim ws As Worksheet, matchFormula As String, cb1 As String, cb2 As String
Set ws = Sheets("Task Due Dates")
cb1 = TASKS_EQUIP_ID_LIST.Text
cb2 = TASKS_TASK_DUE_LIST.Text
matchFormula = "match(" & Chr(34) & cb1 & cb2 & Chr(34) & ",A:A&B:B,0)"
Worksheets("Task Due Dates").Cells(Evaluate(matchFormula), ws.Columns.Count).End(xlToLeft).Offset(, 1) = Date
On Error GoTo 0

This is what I am using altered from what was posted with the actual comboboxes and reference to the sheet. I added the sheet to the evaluation line to help with a problem I noticed after running the code a few times. If I have the "Task Due Dates" sheet visible everything goes in the right place but if I have a different sheet in the same workbook open the dates don't end up in the right row. Not sure why this is happening. I noticed that when I choose R400/T204 the date goes below the task ID 3 rows down and when I chose R500/T107 the date went beside R500/T106. It is important that the dates end up in the right place without actually viewing the sheet. Do I need to declare the sheet as active?

Equip ID	Task ID
R400	T204	6/29/2020
R500	T107	6/29/2020
R500	T106	6/29/2020
R500	T105
	6/29/2020