ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   worksheet function in event macro (https://www.excelbanter.com/excel-worksheet-functions/231852-worksheet-function-event-macro.html)

Ita
worksheet function in event macro
 
Hi,
I have a code in worksheet_selectionchange that doesnt 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 wasnt 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("B1:D100")

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

Per Jessen
worksheet function in event macro
 
Hi ITA

First, I assume the macro is placed in the code sheet for the desired sheet.

In the macro below I only continue if changes has been made to columns B:C
and if column A cell is numeric.

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
Dim isect As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set isect = Intersect(Target, Columns("B:C"))
If Not isect Is Nothing Then Exit Sub
RowNumber = Target.Row

If Not IsNumeric(Range("A" & RowNumber)) Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 Then
SLookupDivCC = Range("C" & RowNumber).Value
Set RLookupDivCC = Worksheets("LookUpTable").Range("B1:D100")
Range("W" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC , RLookupDivCC, 2, False)
Range("X" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC , RLookupDivCC, 3, False)
ElseIf Target.Column = 3 Then
SLookupRev = Range("B" & RowNumber).Value
Set RLookupRev = Worksheets("LookUpTable").Range("E1:F50")
Range("Y" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupRev, RLookupRev, 2, False)
End If

Application.EnableEvents = True
End Sub

Hopes this helps.

---
Per

"Ita" skrev i meddelelsen
...
Hi,
I have a code in worksheet_selectionchange that doesnt 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 wasnt 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("B1:D100")

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


Per Jessen
worksheet function in event macro
 
ITA,

As the macro shall only fire when a value is changed in column B:C, you
should use a Change event.

Change this line in my macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
to:
Private Sub Worksheet_Change(ByVal Target As Range)

Regards,
Per


"Per Jessen" skrev i meddelelsen
...
Hi ITA

First, I assume the macro is placed in the code sheet for the desired
sheet.

In the macro below I only continue if changes has been made to columns B:C
and if column A cell is numeric.

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
Dim isect As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set isect = Intersect(Target, Columns("B:C"))
If Not isect Is Nothing Then Exit Sub
RowNumber = Target.Row

If Not IsNumeric(Range("A" & RowNumber)) Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 Then
SLookupDivCC = Range("C" & RowNumber).Value
Set RLookupDivCC = Worksheets("LookUpTable").Range("B1:D100")
Range("W" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC , RLookupDivCC, 2,
False)
Range("X" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC , RLookupDivCC, 3,
False)
ElseIf Target.Column = 3 Then
SLookupRev = Range("B" & RowNumber).Value
Set RLookupRev = Worksheets("LookUpTable").Range("E1:F50")
Range("Y" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupRev, RLookupRev, 2, False)
End If

Application.EnableEvents = True
End Sub

Hopes this helps.

---
Per

"Ita" skrev i meddelelsen
...
Hi,
I have a code in worksheet_selectionchange that doesnt 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 wasnt 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("B1:D100")

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

Ita
worksheet function in event macro
 
Hi Per,
thanks for the reply. It's still the same though.

"Per Jessen" wrote:

ITA,

As the macro shall only fire when a value is changed in column B:C, you
should use a Change event.

Change this line in my macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
to:
Private Sub Worksheet_Change(ByVal Target As Range)

Regards,
Per


"Per Jessen" skrev i meddelelsen
...
Hi ITA

First, I assume the macro is placed in the code sheet for the desired
sheet.

In the macro below I only continue if changes has been made to columns B:C
and if column A cell is numeric.

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
Dim isect As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set isect = Intersect(Target, Columns("B:C"))
If Not isect Is Nothing Then Exit Sub
RowNumber = Target.Row

If Not IsNumeric(Range("A" & RowNumber)) Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 Then
SLookupDivCC = Range("C" & RowNumber).Value
Set RLookupDivCC = Worksheets("LookUpTable").Range("B1:D100")
Range("W" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC , RLookupDivCC, 2,
False)
Range("X" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupDivCC , RLookupDivCC, 3,
False)
ElseIf Target.Column = 3 Then
SLookupRev = Range("B" & RowNumber).Value
Set RLookupRev = Worksheets("LookUpTable").Range("E1:F50")
Range("Y" & RowNumber).Value =
Application.WorksheetFunction.VLookup(SLookupRev, RLookupRev, 2, False)
End If

Application.EnableEvents = True
End Sub

Hopes this helps.

---
Per

"Ita" skrev i meddelelsen
...
Hi,
I have a code in worksheet_selectionchange that doesnt 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 wasnt 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("B1:D100")

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


All times are GMT +1. The time now is 04:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com