2nd Updating Cell with VBA?

[Greg777]

Hello,

Whenever cell A1 has a new value, it is recorded in column K. I would like the VBA code to do the same for cell A2 & column L.

Here is the VBA code that I have used for cell A1:

'1st
Private Sub Worksheet_Change(ByVal Target As Range)

Dim AAA As Long

AAA = Range("K" & Rows.Count).End(xlUp).Row

If [A1] <> Range("K" & AAA) Then
    Application.EnableEvents = False
    Range("K" & AAA + 1).Value = [A1].Value
    Application.EnableEvents = True
End If
End Sub

I've attached 2 files.
The file that ends with an 'a' is works as expected.
The file ending in 'b' attempts to do the same thing, but twice, acting on two cells at once and gives an error. I'd like to get this to work, in file 'b'.

Thanks for any help!

Greg

[TMS]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[TMS]

It would look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim AAA As Long
Dim BBB As Long

If Not Intersect(Target, Range("A1")) Is Nothing Then

    AAA = Range("K" & Rows.Count).End(xlUp).Row

    If [A1] <> Range("K" & AAA) Then
        Application.EnableEvents = False
        Range("K" & AAA + 1).Value = [A1].Value
        Application.EnableEvents = True
    End If
    
    Exit Sub
End If

If Not Intersect(Target, Range("B1")) Is Nothing Then

    BBB = Range("L" & Rows.Count).End(xlUp).Row
    
    If [B1] <> Range("L" & BBB) Then
        Application.EnableEvents = False
        Range("L" & BBB + 1).Value = [B1].Value
        Application.EnableEvents = True
    End If

    Exit Sub
End If

End Sub

Regards, TMS

[Greg777]

It is no longer returning an error, but there is no 2nd value in column L. I've attached a 3rd workbook where Cell A4 can be updated, changing the values in A1 & B1. Maybe there it is lacking the ability to register the change to cells A1 & A2? If I directly change the value of the cells, they will give output individually, but of course that is not sufficient. I'd stuck on this one.

[TMS]

You can't have two Worksheet Change Events. You need to combine the code into one module.

Test which cell has been changed at the beginning of the code and use If or Select Case to execute the appropriate code for that cell.


Regards, TMS

[Greg777]

OK. Looks like this one isn't possible in Excel. Closing thread.