I
Ita
Hi,
I have a code in worksheet_selectionchange that doesn’t seem to work.
I intend to do vlookup in column W to Y based on changes column B & C (if
column A ISNUMBER).
When I enter a value in both column B & C, column W to Y wasn’t updated even
though the value is there in the lookuptable. Is there something wrong?
Here is the code.
Option Explicit
Dim RowNumber As Long
Dim RLookupDivCC As Variant, SLookupDivCC As String, SLookupUpdDiv As String
Dim SLookupUpdCC As String
Dim RLookupRev As Range, SLookupRev As String, SLookupUpdRev As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
RowNumber = Target.Row
Select Case Target.Column
Case 1
If IsText(Range("A" & RowNumber)) Then
GoTo ENDIT:
Case 2
SLookupDivCC = Range("C" & RowNumber).Value
Set RLookupDivCC = Worksheets("LookUpTable").Range("B1100")
Range("W" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC, RLookupDivCC, 2, False)
Range("X" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC, RLookupDivCC, 3, False)
Case 3
SLookupRev = Range("B" & RowNumber).Value
Set RLookupRev = Worksheets("LookUpTable").Range("E1:F50")
Range("Y" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupRev, RLookupRev, 2, False)
End Select
ENDIT
Application.EnableEvents = True
End Sub
thank you
ITA
I have a code in worksheet_selectionchange that doesn’t seem to work.
I intend to do vlookup in column W to Y based on changes column B & C (if
column A ISNUMBER).
When I enter a value in both column B & C, column W to Y wasn’t updated even
though the value is there in the lookuptable. Is there something wrong?
Here is the code.
Option Explicit
Dim RowNumber As Long
Dim RLookupDivCC As Variant, SLookupDivCC As String, SLookupUpdDiv As String
Dim SLookupUpdCC As String
Dim RLookupRev As Range, SLookupRev As String, SLookupUpdRev As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
RowNumber = Target.Row
Select Case Target.Column
Case 1
If IsText(Range("A" & RowNumber)) Then
GoTo ENDIT:
Case 2
SLookupDivCC = Range("C" & RowNumber).Value
Set RLookupDivCC = Worksheets("LookUpTable").Range("B1100")
Range("W" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC, RLookupDivCC, 2, False)
Range("X" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC, RLookupDivCC, 3, False)
Case 3
SLookupRev = Range("B" & RowNumber).Value
Set RLookupRev = Worksheets("LookUpTable").Range("E1:F50")
Range("Y" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupRev, RLookupRev, 2, False)
End Select
ENDIT
Application.EnableEvents = True
End Sub
thank you
ITA