• 0

Excel Programming Question


Question

Here's my code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim x As Double

Dim i As Integer

Set VRange = Range("a1:a10")

x = Cells(Target.Row, Target.Column)

For Each cell In Target

If Union(cell, VRange).Address = VRange.Address Then

Cells(Target.Row, Target.Column + 1) = x

End If

Next cell

End Sub

.... so if A1 to A10 changes it will write the value to cell B1 to B10, and it works when I manually enter in a value.

However, what I"m really intersted in is using this with stock prices. in cells A1 to A10 there are stock prices that are constantly updating, I do this my typing in =GETSTOCK|'L1'!'BBY;lp', however when the price changes, my program doesn't work where it'll output the change to cell B1 to B10 and I'm not sure why.

Thanks

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0
Here's my code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim x As Double

Dim i As Integer

Set VRange = Range("a1:a10")

x = Cells(Target.Row, Target.Column)

For Each cell In Target

If Union(cell, VRange).Address = VRange.Address Then

Cells(Target.Row, Target.Column + 1) = x

End If

Next cell

End Sub

.... so if A1 to A10 changes it will write the value to cell B1 to B10, and it works when I manually enter in a value.

However, what I"m really intersted in is using this with stock prices. in cells A1 to A10 there are stock prices that are constantly updating, I do this my typing in =GETSTOCK|'L1'!'BBY;lp', however when the price changes, my program doesn't work where it'll output the change to cell B1 to B10 and I'm not sure why.

Thanks

This might be too simple, but why not allow for B1 = A1, etc.? This way, when A1 -> A10 change, the B will mirror that change.

Edit:

If you want a value in B, instead of a formula, this should work:

Private Sub Worksheet_Calculate()
For i = 1 To 10
Range("B" & i).Value = Range("A" & i).Value
Next i
End Sub

Edited by thevink
Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.