Newbie question regarding using Sub Worksheet_change()
 

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

Newbie question regarding using Sub Worksheet_change()
 

If Intersect([B:B], Target) Is Nothing Then Exit Sub

HTH

Charles

titus wrote:
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

Newbie question regarding using Sub Worksheet_change()
 

Genius ! Thank you very much, was scratching my head since afternoon.