Change_Event troubles, no error & no work.

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

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.

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 thekey 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
 
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.
Click to expand...

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 Howard,



Am Wed, 15 Jan 2014 03:48:06 -0800 (PST) schrieb L. Howard:







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.
Click to expand...

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
 
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.
Click to expand...

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.
 
Hi Howard,



Am Wed, 15 Jan 2014 04:38:35 -0800 (PST) schrieb L. Howard:






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.
Click to expand...


Thanks Claus, very nice! Works well!

And answers my question about a variable range too.

Appreciate it.

Regards,
Howard
 
Hi Howard,



Am Wed, 15 Jan 2014 04:38:35 -0800 (PST) schrieb L. Howard:






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.
Click to expand...


Thanks Claus. Works quite well!

And answers my question about a variable range too.

Regards,
Howard
 
Back
Top