VBA - Auto change value in Table

[KowieT]

Hi,
I've got a table imported from SQL. The value in Column A to end of the range must change when you manualy enter a value in the corresponding row in Column B.
The new value for Column A is calculated in Column C.

I'm using this code in VBA, it seems to work fine till you get to row 54 then multiple values starts changing in Column A
Possibly I'm defining the range incorrectly.
Any suggestions will be appreciated.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Not Intersect(Range("B" & i), Target) Is Nothing Then
Range("A" & i).Value = Range("C" & i).Value

End If
Next i
End Sub

Price Change.xlsm
	A	B	C
1	Value Auto Change	Value Manual Change	Formula to calculate what the cell in column A must change to
2	8.70	10.00	8.70
3	10.43	12.00	10.43
4	5.22	6.00	5.22
5	8.70	10.00	8.70
6	17.39	20.00	17.39
7	26.09	30.00	26.09
8	86.96	100.00	86.96
9	8.70	10.00	8.70
10	52.17	5.00	4.35
11	8.70	10.00	8.70
12	86 956.52	1.00	0.87
13	4.35	5.00	4.35
14	8.70	10.00	8.70
15	17.39	20.00	17.39
16	43.48	10.00	8.70
17	4.35	5.00	4.35
18	17.39	20.00	17.39
19	4.35	5.00	4.35
20	4.35	5.00	4.35
21	4.35	5.00	4.35
22	4.35	5.00	4.35
23	4.35	5.00	4.35
24	8.70	10.00	8.70
25	173.91	200.00	173.91
26	26.09	30.00	26.09
27	26.09	30.00	26.09
28	434.78	500.00	434.78
29	26.09	30.00	26.09
30	260.87	300.00	260.87
31	26.09	30.00	26.09
32	26.09	30.00	26.09
33	26.09	30.00	26.09
34	26.09	30.00	26.09
35	26.09	30.00	26.09
36	27.09	37.95	33.00
37	43.48	50.00	43.48
38	26.09	30.00	26.09
39	8.70	10.00	8.70
40	17.39	20.00	17.39
41	43.48	50.00	43.48
42	8.70	10.00	8.70
43	9.70	46.00	40.00
44	10.70	47.15	41.00
45	17.39	20.00	17.39
46	43.48	50.00	43.48
47	43.48	50.00	43.48
48	44.48	51.75	45.00
49	45.48	52.90	46.00
50	52.17	60.00	52.17
51	43.48	50.00	43.48
52	44.48	56.35	49.00
53	43.48	50.00	43.48
54	78.26	90.00	78.26
55	43.48	50.00	43.48
56	44.48	60.95	53.00
57	45.48	62.10	54.00
58	46.48	63.25	55.00
59	47.48	64.40	56.00
Sheet1

Cell Formulas
Range	Formula
A36,A56:A59,A52,A48:A49,A43:A44	A36	=+A35+1
C2:C59	C2	=+B2*100/115

 

[RoryA]

You should always disable events while making changes from a change event, to avoid recursion:

Private Sub Worksheet_Change(ByVal Target As Range)
on error goto clean_up
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
application.enableevents = false
For i = 1 To LastRow
If Not Intersect(Range("B" & i), Target) Is Nothing Then
Range("A" & i).Value = Range("C" & i).Value

End If
Next i
clean_up:
application.enableevents = true
End Sub

 

[KowieT]

You should always disable events while making changes from a change event, to avoid recursion:

Private Sub Worksheet_Change(ByVal Target As Range)
on error goto clean_up
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
application.enableevents = false
For i = 1 To LastRow
If Not Intersect(Range("B" & i), Target) Is Nothing Then
Range("A" & i).Value = Range("C" & i).Value

End If
Next i
clean_up:
application.enableevents = true
End Sub

Thanks RoryA, working