Help on Worksheet_change (I think!)
 

Hi,

Using Excel 2007

I have a macro (Sub UpdatePrices() )with the following code...

Range("B199:b279").Select
Selection.Copy
Range("xfd199").End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("xfd199").End(xlToLeft).Select
If ActiveCell.Column < 31 Then Exit Sub
ActiveCell.Offset(0, -30).Select
Selection.Copy
Range("c126").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True

which I need to run everytime the value in cell A126 changes (it's based on
a live feed & updates every second)

I think what I need to do is incorporate Worksheet_Change into this somehow
using cell A126 as the key cell. However, elsewhere in teh worksheet this Sub
is used & when I tried to incorporate "my" macro into a new Worksheet_Change
Sub I received an error telling me that there was an ambiguous reference (or
words to that effect!)

Do I need to incorporate the bit about A126 into the first Worksheet_Change
& then tell it to run "my macro"? If so, suggestions welcome!

Cheers
Colin Foster

Help on Worksheet_change (I think!)
 

Your live feed may not activate the change event anyway. In some other cell,
say Z100, enter:

=A126

When A126 changes, Z100 will change also. Now we can use the Calculate
event macro to run your bit of code.
--
Gary''s Student - gsnu200842


"Colin Foster" wrote:

Hi,

Using Excel 2007

I have a macro (Sub UpdatePrices() )with the following code...

Range("B199:b279").Select
Selection.Copy
Range("xfd199").End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("xfd199").End(xlToLeft).Select
If ActiveCell.Column < 31 Then Exit Sub
ActiveCell.Offset(0, -30).Select
Selection.Copy
Range("c126").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True

which I need to run everytime the value in cell A126 changes (it's based on
a live feed & updates every second)

I think what I need to do is incorporate Worksheet_Change into this somehow
using cell A126 as the key cell. However, elsewhere in teh worksheet this Sub
is used & when I tried to incorporate "my" macro into a new Worksheet_Change
Sub I received an error telling me that there was an ambiguous reference (or
words to that effect!)

Do I need to incorporate the bit about A126 into the first Worksheet_Change
& then tell it to run "my macro"? If so, suggestions welcome!

Cheers
Colin Foster

Help on Worksheet_change (I think!)
 

Hi Gary,
Thanks for this... so I can arrange the "Z100" bit (in fact A126 contains
=D2 which is where the "real" time is, so I'm actually there), but then how
do I build the Calculate Event Macro in to my existing code?

Thanks is advance

Regards
Colin

"Gary''s Student" wrote:

Your live feed may not activate the change event anyway. In some other cell,
say Z100, enter:

=A126

When A126 changes, Z100 will change also. Now we can use the Calculate
event macro to run your bit of code.
--
Gary''s Student - gsnu200842


"Colin Foster" wrote:

Hi,

Using Excel 2007

I have a macro (Sub UpdatePrices() )with the following code...

Range("B199:b279").Select
Selection.Copy
Range("xfd199").End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("xfd199").End(xlToLeft).Select
If ActiveCell.Column < 31 Then Exit Sub
ActiveCell.Offset(0, -30).Select
Selection.Copy
Range("c126").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True

which I need to run everytime the value in cell A126 changes (it's based on
a live feed & updates every second)

I think what I need to do is incorporate Worksheet_Change into this somehow
using cell A126 as the key cell. However, elsewhere in teh worksheet this Sub
is used & when I tried to incorporate "my" macro into a new Worksheet_Change
Sub I received an error telling me that there was an ambiguous reference (or
words to that effect!)

Do I need to incorporate the bit about A126 into the first Worksheet_Change
& then tell it to run "my macro"? If so, suggestions welcome!

Cheers
Colin Foster

Help on Worksheet_change (I think!)
 

First make sure that Sub UpdatePrices() is in a standard module. Then insert
the following event macro in the worksheet code area:

Private Sub Worksheet_Calculate()
Call UpdatePrices
End Sub

--
Gary''s Student - gsnu200842


"Colin Foster" wrote:

Hi Gary,
Thanks for this... so I can arrange the "Z100" bit (in fact A126 contains
=D2 which is where the "real" time is, so I'm actually there), but then how
do I build the Calculate Event Macro in to my existing code?

Thanks is advance

Regards
Colin

"Gary''s Student" wrote:

Your live feed may not activate the change event anyway. In some other cell,
say Z100, enter:

=A126

When A126 changes, Z100 will change also. Now we can use the Calculate
event macro to run your bit of code.
--
Gary''s Student - gsnu200842


"Colin Foster" wrote:

Hi,

Using Excel 2007

I have a macro (Sub UpdatePrices() )with the following code...

Range("B199:b279").Select
Selection.Copy
Range("xfd199").End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("xfd199").End(xlToLeft).Select
If ActiveCell.Column < 31 Then Exit Sub
ActiveCell.Offset(0, -30).Select
Selection.Copy
Range("c126").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True

which I need to run everytime the value in cell A126 changes (it's based on
a live feed & updates every second)

I think what I need to do is incorporate Worksheet_Change into this somehow
using cell A126 as the key cell. However, elsewhere in teh worksheet this Sub
is used & when I tried to incorporate "my" macro into a new Worksheet_Change
Sub I received an error telling me that there was an ambiguous reference (or
words to that effect!)

Do I need to incorporate the bit about A126 into the first Worksheet_Change
& then tell it to run "my macro"? If so, suggestions welcome!

Cheers
Colin Foster

Help on Worksheet_change (I think!)
 

Hi Gary,
Thanks for the swift reply.

"Worksheet code area" presumably the code bit attached to Sheet1 (where the
data is?
Also, don't I have to refer to the cell that I need to know has changed (A126?

Cheers
Colin

"Gary''s Student" wrote:

First make sure that Sub UpdatePrices() is in a standard module. Then insert
the following event macro in the worksheet code area:

Private Sub Worksheet_Calculate()
Call UpdatePrices
End Sub