Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event troubles, no error & no work.
I want to house two different change events in this macro.
The second one at the bottom seems to be okay. It's the AW6:AW100 range I can't seem to figure out. The "For Each c In AWrng" code works in a conventional macro but I need it to run on a change within the range specified. The values in column AW are an external download of dollar amounts, so paste special is not needed, and run from a conventional macro they transfer okay. Looking for c not blank and c is greater than 0. The greater than 0 is the key here inasmuch as if the value is (1.00), by accounting formatting, then do not transfer the negative value, but it can change on its own to greater than 0 and that's when I need it to fire. What I have does nothing so I am wondering if I have the wrong wording in the "If Not Intersect(Target, Range("AW6:AW100")) Is Nothing _ Or Target.Cells.count 1 Then Exit Sub" statement. I've tried it without the "Not" also, and no go. Also without the "cells.count 1" Are both sets of the .Enable events FALSE/TRUE necessary? Thanks. Howard Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False With Target If Not Intersect(Target, Range("AW6:AW100")) Is Nothing _ Or Target.Cells.count 1 Then Exit Sub For Each c In AWrng If c.Offset(, -3) = "" Then If c 0 Then c.Offset(, -3) = c End If End If Next End With Application.EnableEvents = True Application.EnableEvents = False With Target If Not Intersect(Target, Range("BI48:BJ65")) Is Nothing Then Cells(.Row, 46) = Cells(.Row, 49) End If End With Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event troubles, no error & no work.
Hi Howard,
Am Wed, 15 Jan 2014 03:48:06 -0800 (PST) schrieb L. Howard: I want to house two different change events in this macro. The second one at the bottom seems to be okay. It's the AW6:AW100 range I can't seem to figure out. try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AW6:AW100,BI48:BJ65")) Is Nothing _ Or Target.Count 1 Then Exit Sub Application.EnableEvents = False Select Case Target.Column Case 49 If Target.Offset(, -3) = "" Then If Target 0 Then Target.Offset(, -3) = Target End If End If Application.EnableEvents = True Application.EnableEvents = False Case 61, 62 Cells(Target.Row, 46) = Cells(Target.Row, 49) End Select Application.EnableEvents = True End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event troubles, no error & no work.
On Wednesday, January 15, 2014 4:03:52 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 15 Jan 2014 03:48:06 -0800 (PST) schrieb L. Howard: I want to house two different change events in this macro. The second one at the bottom seems to be okay. It's the AW6:AW100 range I can't seem to figure out. try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AW6:AW100,BI48:BJ65")) Is Nothing _ Or Target.Count 1 Then Exit Sub Application.EnableEvents = False Select Case Target.Column Case 49 If Target.Offset(, -3) = "" Then If Target 0 Then Target.Offset(, -3) = Target End If End If Application.EnableEvents = True Application.EnableEvents = False Case 61, 62 Cells(Target.Row, 46) = Cells(Target.Row, 49) End Select Application.EnableEvents = True End Sub Regards Claus B. Hi Claus, Thanks for taking a look. For some reason it does not fire if I change a cell in AW6:AW100. This is the code I can run from a button and it works fine. Sub AWcolToATcol() Dim c As Range Dim Lc As Long, lr As Long Dim Arng As Range lr = Cells(Rows.count, 49).End(xlUp).Row Set Arng = Range("AW6:AW" & lr) For Each c In Arng If c.Offset(, -3) = "" Then If c 0 Then c.Offset(, -3) = c End If End If Next End Sub Also, can you do a variable range with a change event macro? Seems I read somewhere you have to declare the range Global in a standard module or something like that. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event troubles, no error & no work.
Hi Howard,
Am Wed, 15 Jan 2014 04:38:35 -0800 (PST) schrieb L. Howard: For some reason it does not fire if I change a cell in AW6:AW100. try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AW:AW,BI48:BJ65")) Is Nothing _ Or Target.Count 1 Then Exit Sub Dim rngAW As Range Dim LRow As Long Dim c As Range LRow = Cells(Rows.Count, 49).End(xlUp).Row Set rngAW = Range("AW6:AW" & LRow) Select Case Target.Column Case 49 For Each c In rngAW If c.Offset(, -3) = "" Then If c 0 Then c.Offset(, -3) = c End If End If Next Case 61, 62 Cells(Target.Row, 46) = Cells(Target.Row, 49) End Select End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event troubles, no error & no work.
On Wednesday, January 15, 2014 5:14:54 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 15 Jan 2014 04:38:35 -0800 (PST) schrieb L. Howard: For some reason it does not fire if I change a cell in AW6:AW100. try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AW:AW,BI48:BJ65")) Is Nothing _ Or Target.Count 1 Then Exit Sub Dim rngAW As Range Dim LRow As Long Dim c As Range LRow = Cells(Rows.Count, 49).End(xlUp).Row Set rngAW = Range("AW6:AW" & LRow) Select Case Target.Column Case 49 For Each c In rngAW If c.Offset(, -3) = "" Then If c 0 Then c.Offset(, -3) = c End If End If Next Case 61, 62 Cells(Target.Row, 46) = Cells(Target.Row, 49) End Select End Sub Regards Claus B. Thanks Claus, very nice! Works well! And answers my question about a variable range too. Appreciate it. Regards, Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event troubles, no error & no work.
On Wednesday, January 15, 2014 5:14:54 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Wed, 15 Jan 2014 04:38:35 -0800 (PST) schrieb L. Howard: For some reason it does not fire if I change a cell in AW6:AW100. try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AW:AW,BI48:BJ65")) Is Nothing _ Or Target.Count 1 Then Exit Sub Dim rngAW As Range Dim LRow As Long Dim c As Range LRow = Cells(Rows.Count, 49).End(xlUp).Row Set rngAW = Range("AW6:AW" & LRow) Select Case Target.Column Case 49 For Each c In rngAW If c.Offset(, -3) = "" Then If c 0 Then c.Offset(, -3) = c End If End If Next Case 61, 62 Cells(Target.Row, 46) = Cells(Target.Row, 49) End Select End Sub Regards Claus B. Thanks Claus. Works quite well! And answers my question about a variable range too. Regards, Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change_Event troubles, no error & no work.
Sorry for the double post.
Hope I am not having posting problems again. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No response to change_event | Excel Programming | |||
OnTime Change_Event macro AND copy to sheet2 macro trouble | Excel Programming | |||
Combobox Change_Event | Excel Programming | |||
IF troubles | Excel Worksheet Functions | |||
Change_Event | Excel Programming |