Need Advise !.. Is it the best way I found in WorkSheets_Change event

[halimnurikhwan]

Hi all,

I write this code if the changes in the specific range so the other
specific range is affected by the changes of first specific range, so I
write code like :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Or Target.Address = "$B$3" Or _
Target.Address = "$B$4" Or Target.Address = "$B$5" Or _
Target.Address = "$B$6" Or Target.Address = "$C$2" Or _
Target.Address = "$C$3" Or Target.Address = "$C$4" Or _
Target.Address = "$C$5" Or Target.Address = "$C$6" Then
'Assume that the original range is Range("B2:C6"), I do with Or because
I want to react them by each cell changing
[L2] = WorksheetFunction.CountIf(Range("B2:C6"), [I2])
[L3] = WorksheetFunction.CountIf(Range("B2:C6"), [I3])
[L4] = WorksheetFunction.CountIf(Range("B2:C6"), [I4])
[L5] = WorksheetFunction.CountIf(Range("B2:C6"), [I5])
[L6] = WorksheetFunction.CountIf(Range("B2:C6"), [I6])
[M2] = [L2] * [K2]
[M3] = [L3] * [K3]
[M4] = [L4] * [K4]
[M5] = [L5] * [K5]
[M6] = [L6] * [K6]
End If
End Sub

If you have other way better than the please advise me and post them
and higly appreciated ...

Regards,
halim

 

[NickHK]

You can shorten it with

If Not Intersect(Target.Address, range("B2:C6")) is nothing then
....
But do you need this at all, as you formulae in L2:M6 do not change.
Just enter the formulae on the WS once.

NickHK

 

[halimnurikhwan]

hi nick,

Thats not work, I mean If I change one of range b2:c6 then the summary
count directly
Error still occured
thanks,

hAlim


NickHK menuliskan:

 

[NickHK]

Not sure what the problem is, but you do not need code for this.
Just enter the formulae on the worksheet.
Then you see the relationship between the cell, instead of a range of
numbers.

But if you insist on using code, at least put an
application.enableevents=false/true around you code, otherwise the _Change
event keeps firing for no reason.

NickHK

 

[halimnurikhwan]

Hi Nick,

thank you very much for your quick advise,
I'll try that ...

thanks,

halim

NickHK menuliskan: