| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete a Column when in a Macro-Worksheet Event? | Excel Worksheet Functions | |||
| Event Macro running another macro inside | Excel Discussion (Misc queries) | |||
| Event macro that targets specific worksheet | Excel Discussion (Misc queries) | |||
| It seems to me that I need an event Macro, | Excel Worksheet Functions | |||
| Event Macro | Excel Discussion (Misc queries) | |||
Linear Mode
