T
titus
Hi,
Column A contains X axis data
Column B contains Y axis data
Column C contains formula which depends on X and Y axis data
Now as I change Y value in excel I want Column C to be changed
automatically. I can do this without VBA by inserting the formula in
Column C but I was just wondering how it is possible in VBA. I tried
running the code inside the Sub Worksheet_change(by val target as
range) but whenever Y is changed C does not change. any idea where Im
going wrong.
Here is part of the code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Say when I use Target.address ="$B$2" and change B2 in excel the code
runs, but I want to change any cell in column B, so how do I go about
it ? '
Dim S As Double, I As Double
Dim x As Integer
S = Application.WorksheetFunction.Slope(Range("G2:G7"), Range("F2:F7"))
I = Application.WorksheetFunction.Intercept(Range("G2:G7"),
Range("F2:F7"))
For x = 2 To 7
Cells(x, 3) = S * Cells(x, 2) + I ' This is column C which
should update according to the formula'
Next x
End Sub
Thanks
Titus
Column A contains X axis data
Column B contains Y axis data
Column C contains formula which depends on X and Y axis data
Now as I change Y value in excel I want Column C to be changed
automatically. I can do this without VBA by inserting the formula in
Column C but I was just wondering how it is possible in VBA. I tried
running the code inside the Sub Worksheet_change(by val target as
range) but whenever Y is changed C does not change. any idea where Im
going wrong.
Here is part of the code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Say when I use Target.address ="$B$2" and change B2 in excel the code
runs, but I want to change any cell in column B, so how do I go about
it ? '
Dim S As Double, I As Double
Dim x As Integer
S = Application.WorksheetFunction.Slope(Range("G2:G7"), Range("F2:F7"))
I = Application.WorksheetFunction.Intercept(Range("G2:G7"),
Range("F2:F7"))
For x = 2 To 7
Cells(x, 3) = S * Cells(x, 2) + I ' This is column C which
should update according to the formula'
Next x
End Sub
Thanks
Titus