excel vba on cell change error

Question

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Range = Range("A1") Then
    If Range("A1").Value <> Range("A2").Value Then
        Range("C1").Value = Range("C1").Value + 1
        Range("A2").Value = Range("A1").Value
    End If
End If
End Sub

that's the code however when i copy paste a set off cell, let's say 2 columns and 3 rows it produce runtime error 13 type mismatch on line

If Target.Range = Range("A1") Then

why? i simply wants the vba to do something everytime cell A1 changes the value of A1 itself is an excel sum formula

Community · Accepted Answer · 2017-05-23 12:27:13Z

You get type-missmatch error, becase you're trying to compare range (containing many cells) with single cell. If you want to do something every time cell A1 changed, use this one instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ErrHandler

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Range("A1").Value <> Range("A2").Value Then
            Range("C1").Value = Range("C1").Value + 1
            Range("A2").Value = Range("A1").Value
        End If
    End If

ExitHere:
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    Resume ExitHere
End Sub

also note that I'm using Application.EnableEvents = False - it's a good habbit for Worksheet_Change event to use it. It prevents code from infinity firing itself each time you change any cell in event handler code.

UPD:

Btw, the value of A1 itself is an excel sum formula - you can't track changes of formula using above approach. I covered in details how you can do it in this question: Using Worksheet_Calculate event for tracking changes of formula

Alan K · Accepted Answer · 2014-04-11 11:50:27Z

1

Simoco's answer should work for you. Another way (the one I usually use, though only out of habit) is to compare the addresses:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A1").Address Then
    If Range("A1").Value <> Range("A2").Value Then
        Range("C1").Value = Range("C1").Value + 1
        Range("A2").Value = Range("A1").Value
    End If
End If
End Sub

answered Apr 11, 2014 at 11:50

Alan K

2,0173 gold badges20 silver badges30 bronze badges

2 Comments

Dmitry Pavliv Over a year ago

it's not working, since OP said that i copy paste a set off cell, let's say 2 columns and 3 rows, in that case Target.Address would be something like this: $A$1:$B$3, which obviously not equal $A$1

Alan K Over a year ago

Obviously. Your reading of what he's doing may be different to my reading of it but my reading is that he wants this to fire ONLY when he makes a change that affects the formula in A1 by itself (hence the equals statement in the original code) and not when he makes a change which may INCLUDE A1. I could be wrong, of course.

Excel Developers · Accepted Answer · 2014-04-11 15:14:49Z

1

You are getting an error because Target.Range is not defined. You should either just refer to Target (a Range Object) or Target.Address (the address of the Range Object). Secondly, depending on the context, Range("A1") refers to either the cell A1 itself (a Range Object) or the value in cell A1 (a literal value). You need to carefully think what you want to compare to what.

If, as you said, you want the comparison done whenever the value in Range("A1") changes then you should follow Simoco's suggestion.

answered Apr 11, 2014 at 15:14

Excel Developers

2,8352 gold badges24 silver badges36 bronze badges

Collectives™ on Stack Overflow

excel vba on cell change error

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related