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

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

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

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

wrote in message
ps.com...
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

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

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:
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

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

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

wrote in message
ps.com...
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:
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

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

Hi Nick,

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

thanks,

halim

NickHK menuliskan:
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

wrote in message
ps.com...
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:
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