VBA to find value in a range and paste in adjacent cell

[BMil8]

Hello, I have worksheet "Summary" with an account name in cell B4 (changes based on slicer selection). A user enters a comment in cell AC5. I'm looking for a macro that will take that comment and paste it into worksheet "Comments" in column D, after locating the account name from the Summary worksheet in a list that can be found in column B. Let me know if you need more info.

Thanks!

 

[Skyybot]

Oh yeah. We need more info.

 

[jolivanes]

Put this in the Summary Sheet Module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$AC$5" Then
        Sheets("Comments").Columns(2).Find(Sheets("Summary").Cells(4, 2).Value, , , , 1).Offset(, 2).Value = Sheets("Summary").Cells(5, 29).Value
    End If
End Sub

 

[sericom]

Try this

Sub applyCommentToAccount()
    Set rng = Sheets("Comments").Range("B:B").Find(Sheets("Summary").Range("B4"), , xlValues, xlWhole)
    If Not rng Is Nothing Then
        rng.Offset(, 2).Value = Sheets("Summary").Range("AC5")
    Else
        MsgBox "No matching Account Name"
    End If
End Sub

 

[jolivanes]

Or, if you want to run it from a button, in a regular module:

Sub Copy_Comment()
Dim shS As Worksheet, shC As Worksheet, acc As String, comm As String
Set shS = Worksheets("Summary")
Set shC = Worksheets("Comments")
acc = shS.Cells(4, 2).Value
comm = shS.Cells(5, 29).Value
    shC.Columns(2).Find(acc, , , , 1).Offset(, 2).Value = comm
End Sub

 

[jolivanes]

Looks like I like commas. There should be just 3 commas, instead of 4, in the Find line.

 

[BMil8]

Looks like I like commas. There should be just 3 commas, instead of 4, in the Find line.

Thanks. I'm receiving an error "Run Time Error 91. Object Variable or With block variable not set." The "shC.Columns(2).Find(acc, , , 1).Offset(, 2).Value = comm". Tried researching and it says the object isn't Set. I'm not sure what that means though.

 

[BMil8]

Try this

Sub applyCommentToAccount()
    Set rng = Sheets("Comments").Range("B:B").Find(Sheets("Summary").Range("B4"), , xlValues, xlWhole)
    If Not rng Is Nothing Then
        rng.Offset(, 2).Value = Sheets("Summary").Range("AC5")
    Else
        MsgBox "No matching Account Name"
    End If
End Sub

This gets the job done. Thank you!