pause/re-run macro to enable DDE updating
 

hello, please check the macro i've writen:

Sub Macro1()

Dim i As Integer

For i = 1 To 100000
If Worksheets("sheet1").Range("PXLAST").Value =
Worksheets("sheet1").Range("init").Offset(i, 0).Value Then
Else
Worksheets("sheet1").Range("PXLAST").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next

End Sub

*on the PXLAST cell i have a formula that gets updated via a DDE link:
=blp("ukx index", "last price")
*this returns the real time price of FTSE100
my macro is supposed to copy this value down 100000 times or so whenever
there is a change from prev value.
*however i can't get the PXLAST cell to get updated while the macro is
running, so i wanted to introduce a pause in the programming that would allow
the DDE to update before having the macro running again.
*my code is also no working properly because the macro will execute the ELSE
even if the IF clause is verified. as at now, my macro is copying down 6527.6
all the time.

can you help me out with code suggestions?

much appreciated.

pause/re-run macro to enable DDE updating
 

I don't use DDE, but IIRC whilst DDE does not fire the Worksheet_Change
events, if you have a cell dependant on that cells value, the Calculate
event will fire. Run your code there.
Not sure about the 100000 rows, unless you have XL2007, but then
<apparently calculation time will be long.

NickHK

"ALVESM" wrote in message
...
hello, please check the macro i've writen:

Sub Macro1()

Dim i As Integer

For i = 1 To 100000
If Worksheets("sheet1").Range("PXLAST").Value =
Worksheets("sheet1").Range("init").Offset(i, 0).Value Then
Else
Worksheets("sheet1").Range("PXLAST").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next

End Sub

*on the PXLAST cell i have a formula that gets updated via a DDE link:
=blp("ukx index", "last price")
*this returns the real time price of FTSE100
my macro is supposed to copy this value down 100000 times or so whenever
there is a change from prev value.
*however i can't get the PXLAST cell to get updated while the macro is
running, so i wanted to introduce a pause in the programming that would
allow
the DDE to update before having the macro running again.
*my code is also no working properly because the macro will execute the
ELSE
even if the IF clause is verified. as at now, my macro is copying down
6527.6
all the time.

can you help me out with code suggestions?

much appreciated.

pause/re-run macro to enable DDE updating
 

so can you please provide an example. how do i force excel to calculate a
cell's formula?
the latest code i'm using is:

Sub Macro1()

Dim i As Integer

For i = 1 To 1000
Do While Worksheets("sheet1").Range("pxlast").Value =
Worksheets("sheet1").Range("init").Offset(i - 1, 0).Value
Loop
Worksheets("sheet1").Range("pxlast").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next
End Sub


"NickHK" wrote:

I don't use DDE, but IIRC whilst DDE does not fire the Worksheet_Change
events, if you have a cell dependant on that cells value, the Calculate
event will fire. Run your code there.
Not sure about the 100000 rows, unless you have XL2007, but then
<apparently calculation time will be long.

NickHK

"ALVESM" wrote in message
...
hello, please check the macro i've writen:

Sub Macro1()

Dim i As Integer

For i = 1 To 100000
If Worksheets("sheet1").Range("PXLAST").Value =
Worksheets("sheet1").Range("init").Offset(i, 0).Value Then
Else
Worksheets("sheet1").Range("PXLAST").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next

End Sub

*on the PXLAST cell i have a formula that gets updated via a DDE link:
=blp("ukx index", "last price")
*this returns the real time price of FTSE100
my macro is supposed to copy this value down 100000 times or so whenever
there is a change from prev value.
*however i can't get the PXLAST cell to get updated while the macro is
running, so i wanted to introduce a pause in the programming that would
allow
the DDE to update before having the macro running again.
*my code is also no working properly because the macro will execute the
ELSE
even if the IF clause is verified. as at now, my macro is copying down
6527.6
all the time.

can you help me out with code suggestions?

much appreciated.

pause/re-run macro to enable DDE updating
 

You wait for the calculate event to fire. Plenty from Google. Here's one:
http://en.allexperts.com/q/Excel-105...-Real-time.htm

And Stephen Bullen has some stock price/DDE stuff:
http://www.bmsltd.ie/DLCount/DLCount...ile=Chrono.zip
http://www.bmsltd.ie/DLCount/DLCount...e=LastChng.zip
http://www.bmsltd.ie/DLCount/DLCount...e=Previous.zip

NickHK

"ALVESM" wrote in message
...
so can you please provide an example. how do i force excel to calculate a
cell's formula?
the latest code i'm using is:

Sub Macro1()

Dim i As Integer

For i = 1 To 1000
Do While Worksheets("sheet1").Range("pxlast").Value =
Worksheets("sheet1").Range("init").Offset(i - 1, 0).Value
Loop
Worksheets("sheet1").Range("pxlast").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next
End Sub


"NickHK" wrote:

I don't use DDE, but IIRC whilst DDE does not fire the Worksheet_Change
events, if you have a cell dependant on that cells value, the Calculate
event will fire. Run your code there.
Not sure about the 100000 rows, unless you have XL2007, but then
<apparently calculation time will be long.

NickHK

"ALVESM" wrote in message
...
hello, please check the macro i've writen:

Sub Macro1()

Dim i As Integer

For i = 1 To 100000
If Worksheets("sheet1").Range("PXLAST").Value =
Worksheets("sheet1").Range("init").Offset(i, 0).Value Then
Else
Worksheets("sheet1").Range("PXLAST").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next

End Sub

*on the PXLAST cell i have a formula that gets updated via a DDE link:
=blp("ukx index", "last price")
*this returns the real time price of FTSE100
my macro is supposed to copy this value down 100000 times or so
whenever
there is a change from prev value.
*however i can't get the PXLAST cell to get updated while the macro is
running, so i wanted to introduce a pause in the programming that
would
allow
the DDE to update before having the macro running again.
*my code is also no working properly because the macro will execute
the
ELSE
even if the IF clause is verified. as at now, my macro is copying down
6527.6
all the time.

can you help me out with code suggestions?

much appreciated.

pause/re-run macro to enable DDE updating
 

ok then, how do i create a calculate event using my code?
thanks in advance

"NickHK" wrote:

You wait for the calculate event to fire. Plenty from Google. Here's one:
http://en.allexperts.com/q/Excel-105...-Real-time.htm

And Stephen Bullen has some stock price/DDE stuff:
http://www.bmsltd.ie/DLCount/DLCount...ile=Chrono.zip
http://www.bmsltd.ie/DLCount/DLCount...e=LastChng.zip
http://www.bmsltd.ie/DLCount/DLCount...e=Previous.zip

NickHK

"ALVESM" wrote in message
...
so can you please provide an example. how do i force excel to calculate a
cell's formula?
the latest code i'm using is:

Sub Macro1()

Dim i As Integer

For i = 1 To 1000
Do While Worksheets("sheet1").Range("pxlast").Value =
Worksheets("sheet1").Range("init").Offset(i - 1, 0).Value
Loop
Worksheets("sheet1").Range("pxlast").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next
End Sub


"NickHK" wrote:

I don't use DDE, but IIRC whilst DDE does not fire the Worksheet_Change
events, if you have a cell dependant on that cells value, the Calculate
event will fire. Run your code there.
Not sure about the 100000 rows, unless you have XL2007, but then
<apparently calculation time will be long.

NickHK

"ALVESM" wrote in message
...
hello, please check the macro i've writen:

Sub Macro1()

Dim i As Integer

For i = 1 To 100000
If Worksheets("sheet1").Range("PXLAST").Value =
Worksheets("sheet1").Range("init").Offset(i, 0).Value Then
Else
Worksheets("sheet1").Range("PXLAST").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next

End Sub

*on the PXLAST cell i have a formula that gets updated via a DDE link:
=blp("ukx index", "last price")
*this returns the real time price of FTSE100
my macro is supposed to copy this value down 100000 times or so
whenever
there is a change from prev value.
*however i can't get the PXLAST cell to get updated while the macro is
running, so i wanted to introduce a pause in the programming that
would
allow
the DDE to update before having the macro running again.
*my code is also no working properly because the macro will execute
the
ELSE
even if the IF clause is verified. as at now, my macro is copying down
6527.6
all the time.

can you help me out with code suggestions?

much appreciated.