Vlookup and Intersect

[niha]

Hi Guys,
Im working on making a dashboard which requires that as soon as I enter a value in my dashboard sheet in column E, it should find that value in another sheet called data and take up the value from the column adjacent to it and paste it in my dashboard sheet after one column. Im working on a project and need this really badly. This is the code Im using now. Please help

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo MyErrorHandler:

result = Application.WorksheetFunction.Vlookup(Cells(ActiveCell.Row, "E"), Sheets("Data").Range("B:C"), 2, False)

ActiveCell.Offset(0, 2).Value = result

MyErrorHandler:
If Err.Number = 1004 Then
ActiveCell.Offset(0, 2).Value = ""
End If

End Sub

 

[Norie]

Is the posted code not working?

 

[Alex Blakenburg]

Ideally this needs a few additional test conditions but this should get you a little closer to what you seem to be after.
Note:
1) I have swapped your SelectionChange event which triggers every time move from 1 cell to another to a Change event
2) Since the code is updating the sheet, you want to turn events off at the start and then back on again when you finish.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    Dim result As Variant
    
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        On Error GoTo MyErrorHandler:
        
        result = Application.WorksheetFunction.VLookup(Cells(Target.Row, "E"), Sheets("Data").Range("B:C"), 2, False)
        Target.Offset(0, 2).Value = result
    End If
    
CleanExit:
    Application.EnableEvents = True
    Exit Sub
    
MyErrorHandler:
    If Err.Number = 1004 Then
        ActiveCell.Offset(0, 2).Value = ""
        GoTo CleanExit
    End If

End Sub

 

[niha]

Application.EnableEvents = False Dim result As Variant If Not Intersect(Target, Range("E:E")) Is Nothing Then On Error GoTo MyErrorHandler: result = Application.WorksheetFunction.VLookup(Cells(Target.Row, "E"), Sheets("Data").Range("B:C"), 2, False) Target.Offset(0, 2).Value = result End If CleanExit: Application.EnableEvents = True Exit Sub MyErrorHandler: If Err.Number = 1004 Then ActiveCell.Offset(0, 2).Value = "" GoTo CleanExit End If End Sub

This Worked!
Thanks a lott Alexx!!

 

[Alex Blakenburg]

You are welcome. Thanks for letting me know.

 

[niha]

You are welcome. Thanks for letting me know.

Hi Alex!
I really need your help on this one! This is the last step of my project, and Im not able to figure out how to do this. So some values are entered in column D and E. D is the "from location" and E is the "to location", which depicts that everything between these 2 locations (in this case 10-45, including them) is occupied. So whenever a user enters a value in A3 in between 10-45, eg 12,30,40 (including them), an error message should pop up - saying this location is occupied.
Total locations are from 1-350
Also, one more condition here is that this message should occur if the cell adjacent to 45 is empty. If its filled with some text, the message shouldn't pop up.

You are welcome. Thanks for letting me know.

 

[Alex Blakenburg]

You really need to log this as a separate question.
When you do please add whether you are talking the whole column D&E or just a particular row (and which row)
Also an example of something being next to 45.

 

[niha]

You really need to log this as a separate question.
When you do please add whether you are talking the whole column D&E or just a particular row (and which row)
Also an example of something being next to 45.

Done! Let me know if you figure something out please

 

[niha]

You really need to log this as a separate question.
When you do please add whether you are talking the whole column D&E or just a particular row (and which row)
Also an example of something being next to 45.

Also, Im getting an error here. Ive included 2 codes in one rightclick event. So when I run this, it executes first and the debugger is on the breach code(highlighted in red) This is the code Im using

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    
'Audit
Dim vbAns As VbMsgBoxResult
    
    If Not Application.Intersect(Target, Range("J6:J1000")) Is Nothing Then
     vbAns = MsgBox("Did you check the locations manually?", vbYesNo, "Manual Audit")
     
     If vbAns = vbYes Then
     ActiveCell.Value = "Done"
     End If
     
    If vbAns = vbNo Then
    MsgBox "Please check the locations manually first to ensure FIFO."
    
    End If
    
    End If

'Breach Resolve

Dim vbAnswer As VbMsgBoxResult
    
   [COLOR=rgb(184, 49, 47)] If Not Application.Intersect(Target, Range("K16:K1000")) Is Nothing Then[/COLOR]
     vbAnswer = MsgBox("Did you resolve the breach?", vbYesNo, "Breach Check")
     
     If vbAnswer = vbYes Then
     ActiveCell.ClearContents
     ActiveCell.Interior.Color = RGB(255, 255, 255)
     End If
     
    If vbAnswer = vbNo Then
    MsgBox "Please check the location manually to resolve the breach"
    
    End If
    
    End If

End Sub

 

[niha]

If Not Application.Intersect(Target, Range("K16:K1000")) Is Nothing Then

If Not Application.Intersect(Target, Range("K16:K1000")) Is Nothing Then******** This is the line in the code