VBA Change event code

[jamescooper]

Hello, the following code populates the codes of cell K9 if it is changed in the next available row in column AL and K10 in the next available in AV and F4 in the next available in AK.

However, this only happens if the cell is changed manually by entering in. My excel spreadsheet is linked to software where the cell can change without this happening; any idea how I would adapt my code to achieve any changes?

Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim nR As Long


nR = WorksheetFunction.Max(2, Sheets("Bet Angel").Range("AL" & Rows.Count).End(xlUp).Row + 1)


If Not Intersect(Target, Sheets("Bet Angel").Range("K9:K10")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Sheets("Bet Angel").Range("K9").Copy Destination:=Sheets("Bet Angel").Range("AL" & nR)
Sheets("Bet Angel").Range("K10").Copy Destination:=Sheets("Bet Angel").Range("AV" & nR)
Sheets("Bet Angel").Range("F4").Copy Destination:=Sheets("Bet Angel").Range("AK" & nR)


End If


End Sub

 

[offthelip]

try using the worksheet_Calculate event, depending on how the data is coming in you might find that this event works.

 

[jamescooper]

So would it be?

Private Sub Worksheet_Calculate


Dim nR As Long


nR = WorksheetFunction.Max(2, Sheets("Bet Angel").Range("AL" & Rows.Count).End(xlUp).Row + 1)


If Not Intersect(Target, Sheets("Bet Angel").Range("K9:K10")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Sheets("Bet Angel").Range("K9").Copy Destination:=Sheets("Bet Angel").Range("AL" & nR)
Sheets("Bet Angel").Range("K10").Copy Destination:=Sheets("Bet Angel").Range("AV" & nR)
Sheets("Bet Angel").Range("F4").Copy Destination:=Sheets("Bet Angel").Range("AK" & nR)


End If


End Sub</pre>

 

[offthelip]

not quite, you will have noticed that the worksheet calculate event doesn't have a "Target" range. so you need to save the cells you are interested in and check to see if they have changed. So in a standard module declare two variables:

Public oldk9
Public oldk10
Sub test()


End Sub

Then in the worksheet calcuulate event put this code:

Private Sub Worksheet_Calculate()
nr = Cells(Rows.Count, "AL").End(xlUp).Row + 1
inarr = Range("K9:K10")
If inarr(1, 1) <> oldk9 Or inarr(2, 1) <> oldk10 Then


Range("AL" & nr) = inarr(1, 1)
Range("AV" & nr) = inarr(2, 1)
oldk9 = inarr(1, 1)
oldk10 = inarr(2, 1)
'Sheets("Bet Angel").Range("F4").Copy Destination:=Sheets("Bet Angel").Range("AK" & nr)
End If


End Sub

If you find the calculate event doesn't get triggered, try putting an equation in a unused cell which just multiplies the value in K9 by a constant e.g
put :
=2*k9
into cell kk9

 

[jamescooper]

not quite, you will have noticed that the worksheet calculate event doesn't have a "Target" range. so you need to save the cells you are interested in and check to see if they have changed. So in a standard module declare two variables:

Public oldk9
Public oldk10
Sub test()


End Sub

Then in the worksheet calcuulate event put this code:

Private Sub Worksheet_Calculate()
nr = Cells(Rows.Count, "AL").End(xlUp).Row + 1
inarr = Range("K9:K10")
If inarr(1, 1) <> oldk9 Or inarr(2, 1) <> oldk10 Then


Range("AL" & nr) = inarr(1, 1)
Range("AV" & nr) = inarr(2, 1)
oldk9 = inarr(1, 1)
oldk10 = inarr(2, 1)
'Sheets("Bet Angel").Range("F4").Copy Destination:=Sheets("Bet Angel").Range("AK" & nr)
End If


End Sub

If you find the calculate event doesn't get triggered, try putting an equation in a unused cell which just multiplies the value in K9 by a constant e.g
put :
=2*k9
into cell kk9

Thanks, so the first bit goes in say "Module 1"

Then the second section within the sheet you have your data in - e.g. Bet Angel, as above?

 

[offthelip]

yes

 

[jamescooper]

Unfortunately doesn't appear to work :s

 

[offthelip]

put a break point in the code on the if statement and see if it is getting triggered

 

[jamescooper]

Doesn't appear to be.

Do you have a simpler example where say there is a changing value in cell A1 and then it populates in B1, B2, B3, B4 etc..

So I can then adapt my code to suit?

Many thanks.

 

[offthelip]

Have you tried my suggesion of adding an equation which is looking at K9 to force a recalculation?

If you find the calculate event doesn't get triggered, try putting an equation in a unused cell which just multiplies the value in K9 by a constant e.gput :
=2*k9
into cell kk9

If you want to change the code from writing in AL to writing in column B just change all the "AL" to "B"
I doens't make it any simpler!! It is a simple as it can be!!