Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advise pls | Excel Programming | |||
pls advise | Excel Worksheet Functions | |||
I need some Advise | Excel Programming | |||
Advise VBA | Excel Programming | |||
VBA Advise | Excel Programming |