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.

----
mkarja

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







"mkarja" wrote:

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.

----
mkarja

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

"mkarja" wrote:

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.

----
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.)

mkarja wrote:

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.

----
mkarja

--

Dave Peterson

On 30 syys, 15:59, Dave Peterson wrote:
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 wrote:

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.

----
mkarja

--

Dave Peterson

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.

----
mkarja