Change_Event not responding to change made by formula.

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

L. Howard

The problem is with the Case 61, 62.

The values in col 61 and 62 are results of stock prices downloaded from some external source of Excel. So I think that is the reason the

Cells(Target.Row, 46) = Cells(Target.Row, 49)

does not work.

I can make manual changes on the sheet, where I over write the formula, and it works okay.

I tried using Worksheet_Calculate() but could not make that work either.

I was able to make a googled example work where you declare a range in a standard module, the calculate macro in the thisworkbook module and another macro in the sheet module.

Trying to adapt that system to my Case 61, 62 is beyond me.

Am I on the right track?

Thanks.
Howard


Option Explicit


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
If Cells(Target.Row, 61) = 0 Or Cells(Target.Row, 62) = 0 Then Exit Sub
Cells(Target.Row, 46) = Cells(Target.Row, 49)
End Select


End Sub
 
Hi Howard,

Am Sat, 18 Jan 2014 01:22:09 -0800 (PST) schrieb L. Howard:
The problem is with the Case 61, 62.

The values in col 61 and 62 are results of stock prices downloaded from some external source of Excel.
Click to expand...

try:

Private Sub Worksheet_Calculate()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 61).End(xlUp).Row
For Each rngC In Range("BI1:BI" & LRow)
If rngC.Value * rngC.Offset(, 1).Value <> 0 Then
Cells(rngC.Row, 46) = Cells(rngC.Row, 49)
End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AW:AW")) 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)

For Each c In rngAW
If c.Offset(, -3) = "" Then
If c > 0 Then
c.Offset(, -3) = c
End If
End If
Next

End Sub


Regards
Claus B.
 
Hi Howard,



Am Sat, 18 Jan 2014 01:22:09 -0800 (PST) schrieb L. Howard:






try:



Private Sub Worksheet_Calculate()

Dim LRow As Long

Dim rngC As Range



LRow = Cells(Rows.Count, 61).End(xlUp).Row

For Each rngC In Range("BI1:BI" & LRow)

If rngC.Value * rngC.Offset(, 1).Value <> 0 Then

Cells(rngC.Row, 46) = Cells(rngC.Row, 49)

End If

Next

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("AW:AW")) 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)



For Each c In rngAW

If c.Offset(, -3) = "" Then

If c > 0 Then

c.Offset(, -3) = c

End If

End If

Next



End Sub





Regards

Claus B.
Click to expand...


Thanks Claus.

I am assuming it will take a real life stock price up date to properly test the code.

If I do any manual entries it makes the proper copies to column 46 from column 49 but then goes into a continuous loop.

Probably won't know until Monday. (Markets closed)

I trust the code much more than the incredibly complex worksheet I'm using in on.

Howard
 
Hi Howard,

Am Sat, 18 Jan 2014 03:48:48 -0800 (PST) schrieb L. Howard:
If I do any manual entries it makes the proper copies to column 46 from column 49 but then goes into a continuous loop.
Click to expand...

set into the Worksheet_Change event
Application.EnableEvents = False
and at the end to TRUE


Regards
Claus B.
 
Hi Howard,



Am Sat, 18 Jan 2014 03:48:48 -0800 (PST) schrieb L. Howard:






set into the Worksheet_Change event

Application.EnableEvents = False

and at the end to TRUE





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Did as you say, but the loop continues.

However, the code sure does work as far as I can see.

Howard
 
Did as you say, but the loop continues.



However, the code sure does work as far as I can see.



Howard
Click to expand...


Forgot to say, just learned there is a button that brings some other values into play that essentially makes the Calculate code fire.

I'll look for it and maybe do the enable events False/True on that code also.

Howard
 
Forgot to say, just learned there is a button that brings some other values into play that essentially makes the Calculate code fire.



I'll look for it and maybe do the enable events False/True on that code also.



Howard
Click to expand...

I put the events False/True on that code and no continuous loop on the calculate code but now it ignores the 0 values and copies all cells.

Howard
 
I put the events False/True on that code and no continuous loop on the calculate code but now it ignores the 0 values and copies all cells.



Howard
Click to expand...

If I run it like this from a forms button it gets the job done.

Sub SameAsCalc()
'Private Sub Worksheet_Calculate()

Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.count, 61).End(xlUp).Row
For Each rngC In Range("BI6:BI" & LRow)
If rngC.Value * rngC.Offset(, 1).Value <> 0 Then
Cells(rngC.Row, 46) = Cells(rngC.Row, 49)
End If
Next

End Sub

Howard
 
Hi Howard,

Am Sat, 18 Jan 2014 04:37:57 -0800 (PST) schrieb L. Howard:
I put the events False/True on that code and no continuous loop on the calculate code but now it ignores the 0 values and copies all cells.
Click to expand...

for me it works fine and the 0 values are not ignored. Could it be that
your values are not really 0 but shown as 0 because cell format?


Regards
Claus B.
 
Hi Howard,



Am Sat, 18 Jan 2014 04:37:57 -0800 (PST) schrieb L. Howard:






for me it works fine and the 0 values are not ignored. Could it be that

your values are not really 0 but shown as 0 because cell format?





Regards

Claus B.
Click to expand...


I put the code back under Private Sub Worksheet_Calculate().

I have tried formatting as Accounting, General, Currency, Number and it goes into the loop for each format until I hit Esc.

On a forms button, code works okay on all four of those formats.

I provide a "Calculation" by having one each of the 61, 62 cells refer to acell on the sheet. (=BJ1, it's out of the code range) then I change the value of BJ1.

I get the loop for all the formats mentioned and also a couple of Excel crashes.

I'm thinking there is just improper coding practices within this pretty complex worksheet. I have been providing an OP with several answers to "I want this or that" and what I suggest works most of the time. When it doesn'tI offer another until "it works". We are nine pages in the thread and this problem and one other is where I came here for help.

I'm sorta grasping at straws to provide meaningful input.

Howard
 
Hi Howard,

Am Sat, 18 Jan 2014 10:45:12 -0800 (PST) schrieb L. Howard:
I have tried formatting as Accounting, General, Currency, Number and it goes into the loop for each format until I hit Esc.
Click to expand...

in my test workbook I only have the Worksheet_Calculate and the
Worksheet_Change code. Both works fine and I get no loop. The loop nust
be fired from the other code into your workbook.


Regards
Claus B.
 
Hi Howard,



Am Sat, 18 Jan 2014 10:45:12 -0800 (PST) schrieb L. Howard:






in my test workbook I only have the Worksheet_Calculate and the

Worksheet_Change code. Both works fine and I get no loop. The loop nust

be fired from the other code into your workbook.





Regards

Claus B.
Click to expand...


That confirms my suspicions.

I'll go forward with that and see what I can do.

Having your code solutions that I know I can trust will be a help.

Thanks for your time, and advice.

Regards,
Howard
 
Back
Top