Multiple changed cells not detected

[mkarja]

Hi,

I'm trying to make an excel macro that would do something
when a certain cells (B15:AF15) value changes. The problem is
that the code won't detect all the changed cells.
When I for example make a change in cells B12 or B13 then it
would change the values of cells B14 & B15 so in total three
changed cells. The code should calculate the sum of range of
cells from B15 to AF15.
Here's the code.
--------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim janRange As Range
Dim lngCell As Long
Dim rowIndex As Long
Dim targetCount As Long

Set janRange = Sheet1.Range("B15:AF15")

rowIndex = 1

For lngCell = 1 To janRange.Count
For targetCount = 1 To Target.Cells.Count
If janRange(rowIndex, lngCell).Address = Target.Address Then
Sheet1.Cells(7, 7).Value = WorksheetFunction.Sum(janRange)
End If
Next targetCount
Next lngCell

End Sub
----------------------------------------------
Now it never goes inside that If statement.
The Target.Cells.Count is allways 0.

I could of course do those things manually that I'm trying to do
with code now, but where's the fun in that would be

I would appreciate any help with this.

 

[incre-d]

you could simplify your loop

----------------------------------------
If Not Application.Intersect(target, janRange)) Is Nothing Then
' Do my funky thing.
end if
----------------------------------------

More worrying, its' impossible for Target.Cells.Count to = 0 in the
Worksheet_Change event.


In your description though you are implying that you have formula in cells
b14 & b15. Worksheet_Change doesn't consider changes to these formula's as
changes.

but you are more interested in the precedents, so

If Not Application.Intersect(Target, janRange.Precedents) Is Nothing Then
' Do my funky things
End If

 

[Mike H]

Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim janRange As Range
Set janRange = Sheets("Sheet1").Range("B15:AF15")
If Not Intersect(Target, Range("B15:AF15")) Is Nothing Then
Sheets("Sheet1").Cells(7, 7).Value = WorksheetFunction.Sum(janRange)
End If
End Sub

Mike

 

[Dave Peterson]

If you have formulas in B14 and B15 that get reevaluated with changes in B12 and
B13, then you don't want to use the worksheet_change event.

You'd want to use the worksheet_calculate event.

And I think you'd want something like:

Option Explicit
Private Sub Worksheet_Calculate()
Me.Range("G7").Value = Application.Sum(Me.Range("B15:AF15"))
End Sub

But I don't understand why you wouldn't just use:
=sum(b15:af15)
in G7 and drop the event code completely.

(The Me keyword represents the object that holds the codes. In this case, it's
the worksheet that's being recalculated.)

 

[mkarja]

If you have formulas in B14 and B15 that get reevaluated with changes in B12 and
B13, then you don't want to use the worksheet_change event.

You'd want to use the worksheet_calculate event.

And I think you'd want something like:

Option Explicit
Private Sub Worksheet_Calculate()
    Me.Range("G7").Value = Application.Sum(Me.Range("B15:AF15"))
End Sub

But I don't understand why you wouldn't just use:
=sum(b15:af15)
in G7 and drop the event code completely.

(The Me keyword represents the object that holds the codes.  In this case, it's
the worksheet that's being recalculated.)

Thank you all for your help. Very much appreciated.

I have formulas in B14 and B15 that get reevaluated when B12 & B13
changes. Yes, it would propably be best to use the
Worksheet_Calculate()
event. I've got it working now with the help from incre-d and Mike H.
But if I used the Worksheet_Calculate() event I could propably do with
lot
less code. I'll have to see when I have time to change it.
I can't just use =sum(B15:AF15) in G7 because the whole code is a bit
more complex than what I posted here. Theres multiple ranges and some
other stuff also. I just posted that stripped down code since that was
what
I had problems.
Thanks for the Me keyword tip Dave. I had forgotten about that.