Worksheet_Change

Posted by Michael on November 03, 2001 6:09 AM

Hi, I have never written VBA untill today, so bear with me. I have a cell pulling in live data via a stock quote. I need to copy the value in real time to another cell to get the previous quote. My problem is the Worksheet_Change/Copy Range thing I am doing doesn't work sinve it sees the "on update" cell as unchanged since it has the formula to get the live quote. What can I do to copy the value instead of the formula every time the value changes?

Here is what I have:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("Bid")
If Union(Target, VRange).Address = VRange.Address Then
Range("H10").Copy Range("J10")
Range("E10").Copy Range("H10")
End If
End Sub

Thanks for ANY help!!

Michael

Posted by Robb on November 03, 2001 6:59 PM

Michael

Try using the Calculate event instead. This code should work:

Private Sub Worksheet_Calculate()
Range("J10") = Range("H10").Value
Range("H10") = Range("E10").Value
End Sub

Whilst it will be triggered by every recalculation, it will simply
use the same values until they are changed. From the code, I would assume
the range "Bid" may be "E10".

Any help?

Regards

Robb