Got a VBA question for y'all. Haven't been able to find the exact scenario online due to how specific it is.

When I enter a unique ID number into column A (part of a table), an Index-Match auto-populates info into columns B and C. The only issue is that I want to be able to use ctrl+F to identify info in columns B & C, but it doesn't recognize the values that result from the Index-Match formula. Once the Index-Match has done it's work, I'm fine with replacing the formula for that row with the values it returned.

How can I use VBA so that when I enter any value into column M, it will copy and paste the values for the cells in columns B & C in that row only? I only want it to copy and paste the values for B&C in that row since I want the formula to remain for any subsequent rows that I haven't started using yet. Just to clarify, I would want something like:

• ID is entered in A100.
• Info auto-populates in B100 and C100.
• Any info is entered into M100.
• VBA copies and paste values of B100 and C100.
• ID is entered in A101.
• Info auto-populates in B101 and C101 since the Index-Match formula is still present in that row.
• Any info is entered into M101.
• VBA copies and paste values of B100 and C101.

I used the macro recorder to get an idea of what I might need, but I know there is a lot missing since it needs to look at each row independently. I know it probably needs to reference the "active" cell/row/range/etc. at times and also needs to look for any value in cell M for the row (it just used the value I used while recording). Any help would be much appreciated.

I feel like I'm sounding like an ass posting the same thing on all these excel threads, but I've literally solved so many of these issues in the last several months with one thing...

ChatGPT

Ask your question, and it will spit out the macro with instructions on how to use it.

Thanks. I genuinely forgot about ChatGPT. I like to learn how to things myself if I can, but I'm not very well versed in VBA. I probably need to do an official training course on it. Also, the last time I tried using ChatGPT for a much simpler task it couldn't give me a solution that worked for 100% of what I needed. Could have been user error with my prompts, but I was able to find the answer on a forum fairly easily.

It worked this time though (as far as I can tell). I also had to ask it to combine the solution with another Worksheet_Change I already had on the same sheet that lets me select more than one item from a data validation dropdown. The combined code is below for any interested.

Quote:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Dim rng As Range

Application.EnableEvents = True
On Error GoTo Exitsub

' Handle the changes in column M
Set rng = Intersect(Target, Me.Range("M:M"))
If Not rng Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
For Each cell In rng
If cell.Value <> "" Then
' Copy values from columns B and C to the same row
Me.Cells(cell.Row, "B").Value = Me.Cells(cell.Row, "B").Value
Me.Cells(cell.Row, "C").Value = Me.Cells(cell.Row, "C").Value
End If
Next cell
On Error GoTo 0
Application.EnableEvents = True
End If

' Handle the changes in column Q (assuming you are using column Q for your multiple selection)
If Target.Column = 17 Then ' Assuming column Q is column 17
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else
If Target.Value = "" Then GoTo Exitsub
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else
Target.Value = Oldvalue
End If
End If
End If
End If

Exitsub:
Application.EnableEvents = True
End Sub

---

From my experience, ChatGPT requires you to work back and forth to create exactly what tou want. It's not just gonna give you the answer with no understanding on your side. Last one I did wasn't perfect on the first go, but that's when you start telling it to work on various parts of the macro. You can also ask it to explain various parts to you.