How to use VBA code to link drop down box with pasting
Requirement:
The user is trying to get the drop down in B8 to find the matching word in Column A and then paste the comment from Column E into A14 if it matches (essentially just pasting the verbage in E into A14).
The user cannot seem to get the code to run.
Solution:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B8")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Dim aRng As Range
Dim LRow As Long
Dim bWord As String
bWord = Target.Value
With Me
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set aRng = .Range("A2:A" & LRow).Find(bWord, LookIn:=xlValues)
If Not aRng Is Nothing Then
Application.EnableEvents = False
Cells(14, 1).Value = aRng.Offset(, 4).Value
Application.EnableEvents = True
Else
MsgBox "No match found for - " & bWord
End If
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by skywriter.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create a macro that copies all of the tables and charts on a sheet and pastes them as an image on to another sheet |
| How to copy and paste chosen columns based on two criteria |
| How to copy/paste between workbooks with relative referencing |
| How to copy and paste value with V from another file |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
