Change_Event not responding to change made by formula.

[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

 

[Claus Busch]

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.

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.

 

[L. Howard]

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.

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

 

[Claus Busch]

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.

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


Regards
Claus B.

 

[L. Howard]

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

Did as you say, but the loop continues.

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

Howard

 

[L. Howard]

Did as you say, but the loop continues.



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



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

 

[L. 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

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

 

[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.



Howard

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

 

[Claus Busch]

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.

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.

 

[L. Howard]

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.

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

 

[Claus Busch]

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.

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.

 

[L. Howard]

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.

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