creating outcome series from formula
 

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101
appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence
the list of 100 would change dynamically, how would i write that in a macro?

again, thanks very much!
Maurice

"Art" wrote:

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

Maurice,

I made a few changes to the routine including the one to limit it to 100
outcomes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$2" Then Exit Sub
Range("C65536").End(xlUp).Offset(1) = Range("B2")
If Range("C65536").End(xlUp).Address = "$C$102" _
Then Range("$C$2").Delete Shift:=xlUp
End Sub

The previous routine crashed if you cut and pasted more than 1 cell anywhere
in the worksheet. This should work better.

"Maurice" wrote:

that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101
appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence
the list of 100 would change dynamically, how would i write that in a macro?

again, thanks very much!
Maurice

"Art" wrote:

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

thanks a million Art, i made a few small alterations and now it works
perfect, it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target
If Range("C65536").End(xlUp).Address = "$C$10" _
Then Range("$C$2").Delete Shift:=xlUp

End If
End Sub

Thanks a lot and greetings from a sunny Amsterdam..


"Art" wrote:

Maurice,

I made a few changes to the routine including the one to limit it to 100
outcomes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$2" Then Exit Sub
Range("C65536").End(xlUp).Offset(1) = Range("B2")
If Range("C65536").End(xlUp).Address = "$C$102" _
Then Range("$C$2").Delete Shift:=xlUp
End Sub

The previous routine crashed if you cut and pasted more than 1 cell anywhere
in the worksheet. This should work better.

"Maurice" wrote:

that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101
appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence
the list of 100 would change dynamically, how would i write that in a macro?

again, thanks very much!
Maurice

"Art" wrote:

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

Art - i am so close but yet not there! I am running the code but the thing is
that excel doesn't 'read' the changing targetcel unless i put the cursor into
the cell and push enter after each digit. Any way to solve this?

Maurice

"Maurice" wrote:

thanks a million Art, i made a few small alterations and now it works
perfect, it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target
If Range("C65536").End(xlUp).Address = "$C$10" _
Then Range("$C$2").Delete Shift:=xlUp

End If
End Sub

Thanks a lot and greetings from a sunny Amsterdam..


"Art" wrote:

Maurice,

I made a few changes to the routine including the one to limit it to 100
outcomes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$2" Then Exit Sub
Range("C65536").End(xlUp).Offset(1) = Range("B2")
If Range("C65536").End(xlUp).Address = "$C$102" _
Then Range("$C$2").Delete Shift:=xlUp
End Sub

The previous routine crashed if you cut and pasted more than 1 cell anywhere
in the worksheet. This should work better.

"Maurice" wrote:

that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101
appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence
the list of 100 would change dynamically, how would i write that in a macro?

again, thanks very much!
Maurice

"Art" wrote:

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

Maurice,

Thanks for the greetings from sunny Amsterdam -- I'm in cloudy New Jersey -
USA, still waiting for spring.

I think you're in an area that I can't be of much help in. I don't know how
you're getting the data. If it's coming in through another spreadsheet or a
text file through the use of a macro I might be able to help. However if
you've got something set up that sends the data to Excel, I'm probably
unfamiliar with it. In that case, I'd suggest you post another question
about that -- I'm afraid that others who might know the answer may not see
this "older" thread.

On the other hand, if you're using Excel or some file that's being read by a
macro, I might be able to help.

Good luck.

"Maurice" wrote:

Art - i am so close but yet not there! I am running the code but the thing is
that excel doesn't 'read' the changing targetcel unless i put the cursor into
the cell and push enter after each digit. Any way to solve this?

Maurice

"Maurice" wrote:

thanks a million Art, i made a few small alterations and now it works
perfect, it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target
If Range("C65536").End(xlUp).Address = "$C$10" _
Then Range("$C$2").Delete Shift:=xlUp

End If
End Sub

Thanks a lot and greetings from a sunny Amsterdam..


"Art" wrote:

Maurice,

I made a few changes to the routine including the one to limit it to 100
outcomes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$2" Then Exit Sub
Range("C65536").End(xlUp).Offset(1) = Range("B2")
If Range("C65536").End(xlUp).Address = "$C$102" _
Then Range("$C$2").Delete Shift:=xlUp
End Sub

The previous routine crashed if you cut and pasted more than 1 cell anywhere
in the worksheet. This should work better.

"Maurice" wrote:

that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101
appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence
the list of 100 would change dynamically, how would i write that in a macro?

again, thanks very much!
Maurice

"Art" wrote:

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

Art, thanks for the quick reply. I data is being read correctly from the DDE
datafeed (yahoo stock quotes), because i make a calculation with it which
does change when a stockprice changes. However the macro will only run and
add to the colum after i give an enter after a calculation.
Is a refresh function for the macro an option?



"Art" wrote:

Maurice,

Thanks for the greetings from sunny Amsterdam -- I'm in cloudy New Jersey -
USA, still waiting for spring.

I think you're in an area that I can't be of much help in. I don't know how
you're getting the data. If it's coming in through another spreadsheet or a
text file through the use of a macro I might be able to help. However if
you've got something set up that sends the data to Excel, I'm probably
unfamiliar with it. In that case, I'd suggest you post another question
about that -- I'm afraid that others who might know the answer may not see
this "older" thread.

On the other hand, if you're using Excel or some file that's being read by a
macro, I might be able to help.

Good luck.

"Maurice" wrote:

Art - i am so close but yet not there! I am running the code but the thing is
that excel doesn't 'read' the changing targetcel unless i put the cursor into
the cell and push enter after each digit. Any way to solve this?

Maurice

"Maurice" wrote:

thanks a million Art, i made a few small alterations and now it works
perfect, it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target
If Range("C65536").End(xlUp).Address = "$C$10" _
Then Range("$C$2").Delete Shift:=xlUp

End If
End Sub

Thanks a lot and greetings from a sunny Amsterdam..


"Art" wrote:

Maurice,

I made a few changes to the routine including the one to limit it to 100
outcomes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$2" Then Exit Sub
Range("C65536").End(xlUp).Offset(1) = Range("B2")
If Range("C65536").End(xlUp).Address = "$C$102" _
Then Range("$C$2").Delete Shift:=xlUp
End Sub

The previous routine crashed if you cut and pasted more than 1 cell anywhere
in the worksheet. This should work better.

"Maurice" wrote:

that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101
appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence
the list of 100 would change dynamically, how would i write that in a macro?

again, thanks very much!
Maurice

"Art" wrote:

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

Maurice,

I'm sorry but I don't have any experience with DDE. While I'd enjoy
figuring it out and seeing if there's a solution to your situation it will
probably take me longer than you should have to wait. So your best solution
is to make another post with regard to DDE and activating a macro.

Could you tell me how to get access to such a data feed? I would like to
understand how to work with it.

"Maurice" wrote:

Art, thanks for the quick reply. I data is being read correctly from the DDE
datafeed (yahoo stock quotes), because i make a calculation with it which
does change when a stockprice changes. However the macro will only run and
add to the colum after i give an enter after a calculation.
Is a refresh function for the macro an option?



"Art" wrote:

Maurice,

Thanks for the greetings from sunny Amsterdam -- I'm in cloudy New Jersey -
USA, still waiting for spring.

I think you're in an area that I can't be of much help in. I don't know how
you're getting the data. If it's coming in through another spreadsheet or a
text file through the use of a macro I might be able to help. However if
you've got something set up that sends the data to Excel, I'm probably
unfamiliar with it. In that case, I'd suggest you post another question
about that -- I'm afraid that others who might know the answer may not see
this "older" thread.

On the other hand, if you're using Excel or some file that's being read by a
macro, I might be able to help.

Good luck.

"Maurice" wrote:

Art - i am so close but yet not there! I am running the code but the thing is
that excel doesn't 'read' the changing targetcel unless i put the cursor into
the cell and push enter after each digit. Any way to solve this?

Maurice

"Maurice" wrote:

thanks a million Art, i made a few small alterations and now it works
perfect, it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target
If Range("C65536").End(xlUp).Address = "$C$10" _
Then Range("$C$2").Delete Shift:=xlUp

End If
End Sub

Thanks a lot and greetings from a sunny Amsterdam..


"Art" wrote:

Maurice,

I made a few changes to the routine including the one to limit it to 100
outcomes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$2" Then Exit Sub
Range("C65536").End(xlUp).Offset(1) = Range("B2")
If Range("C65536").End(xlUp).Address = "$C$102" _
Then Range("$C$2").Delete Shift:=xlUp
End Sub

The previous routine crashed if you cut and pasted more than 1 cell anywhere
in the worksheet. This should work better.

"Maurice" wrote:

that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101
appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence
the list of 100 would change dynamically, how would i write that in a macro?

again, thanks very much!
Maurice

"Art" wrote:

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands

creating outcome series from formula
 

Art,
The innerworkings of webquiries i don't know about (yet), i use a plug in,
free from quotecat.com which allows you to get autorefresh stock quotes.

The thing is, i don't think it is a problem related to the datafeed. For
instance let say the target variable for the macro is d1 and this cell is the
outcome of adding cells a1 and b1. The point is if i change cell a1, d1
changes automatically, but'our macro doesn't pick up on that, so it doesn't
run.

Anyhow, you helped me a long greatly and i dont want to impose on your time.

thanks again.
Maurice

"Art" wrote:

Maurice,

I'm sorry but I don't have any experience with DDE. While I'd enjoy
figuring it out and seeing if there's a solution to your situation it will
probably take me longer than you should have to wait. So your best solution
is to make another post with regard to DDE and activating a macro.

Could you tell me how to get access to such a data feed? I would like to
understand how to work with it.

"Maurice" wrote:

Art, thanks for the quick reply. I data is being read correctly from the DDE
datafeed (yahoo stock quotes), because i make a calculation with it which
does change when a stockprice changes. However the macro will only run and
add to the colum after i give an enter after a calculation.
Is a refresh function for the macro an option?



"Art" wrote:

Maurice,

Thanks for the greetings from sunny Amsterdam -- I'm in cloudy New Jersey -
USA, still waiting for spring.

I think you're in an area that I can't be of much help in. I don't know how
you're getting the data. If it's coming in through another spreadsheet or a
text file through the use of a macro I might be able to help. However if
you've got something set up that sends the data to Excel, I'm probably
unfamiliar with it. In that case, I'd suggest you post another question
about that -- I'm afraid that others who might know the answer may not see
this "older" thread.

On the other hand, if you're using Excel or some file that's being read by a
macro, I might be able to help.

Good luck.

"Maurice" wrote:

Art - i am so close but yet not there! I am running the code but the thing is
that excel doesn't 'read' the changing targetcel unless i put the cursor into
the cell and push enter after each digit. Any way to solve this?

Maurice

"Maurice" wrote:

thanks a million Art, i made a few small alterations and now it works
perfect, it looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("C65536").End(xlUp).Offset(1, 0) = Range("B1") + Target
If Range("C65536").End(xlUp).Address = "$C$10" _
Then Range("$C$2").Delete Shift:=xlUp

End If
End Sub

Thanks a lot and greetings from a sunny Amsterdam..


"Art" wrote:

Maurice,

I made a few changes to the routine including the one to limit it to 100
outcomes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$2" Then Exit Sub
Range("C65536").End(xlUp).Offset(1) = Range("B2")
If Range("C65536").End(xlUp).Address = "$C$102" _
Then Range("$C$2").Delete Shift:=xlUp
End Sub

The previous routine crashed if you cut and pasted more than 1 cell anywhere
in the worksheet. This should work better.

"Maurice" wrote:

that's great Art, thank you very much! Allow me one follow up question; if i
want to limit my list let's say to 100 outcomes, and when outcome 101
appears, outcome 1 is deleted and replaced by outcome 2 and so forth, hence
the list of 100 would change dynamically, how would i write that in a macro?

again, thanks very much!
Maurice

"Art" wrote:

Maurice,

The only way I can think of is to use a macro. If that's okay, consider the
following.
The formula is in B2. The list of outcomes will be in C. The input cell is
A2.

You need to enter a macro into the sheet that this data is on. The
following macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Range("c65536").End(xlUp).Offset(1) = Range("B2")
End If
End Sub


"Maurice" wrote:

I am working with a formula in which the value of one of the inputcells
changes dynamically (stock quote). What can i do to automatically put the
changing outcome of the formula in a series in a colum?

Can anybody help me out ?

Maurice
Amsterdam, The Netherlands