I am attempting to use VLOOKUP to automatically populate a spreadsheet with data from another workbook via a Private Sub Worksheet_Change. Basically, each time the user double-clicks a cell within Column 2 then this will activate the macro to execute the VLOOKUP and pull in the required data from the other workbook.
I thought that I had it right however I'm only getting #VALUE! when the macro runs. The code I have is shown below. Any help on why this is happening and how to resolve this will be appreciated!!!
I thought that I had it right however I'm only getting #VALUE! when the macro runs. The code I have is shown below. Any help on why this is happening and how to resolve this will be appreciated!!!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ocDrop As String
Dim statusDrop As String
Dim esclDrop As String
If Target.Row <> 1 Then
If Target.Column = 2 Then
If IsEmpty(Target.Value) = False Then
Target.Offset(0, -1).Interior.ColorIndex = 0
Target.Offset(0, 3) = "Open"
Target.Offset(0, 20) = Evaluate("=VLOOKUP(" & Chr(34) & Target.Value & Chr(34) & ",'[MSG - Volume Alerts.xlsm]Sheet1!$A:$AC,4,FALSE)")
End If
End If
End If
End Sub
Last edited by a moderator:
