| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify this a bit
I need to have this section of code modified to include a wide range of
W3:w6000, Z3:Z6000, AH3:AH6000. How would one accomplish that? See code that follows: --------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally If Target.Address < "$AH$3" Then Exit Sub Application.EnableEvents = False Range("W3").Value = Range("W3").Value + 1 Range("Z3").Value = Range("Z3").Value + Target.Value Application.EnableEvents = True End Sub ------------------ THanks, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify this a bit
Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally set rng = Range("AH3:AH6000") If Intersect(target,rng) is nothing Then Exit Sub Application.EnableEvents = False Cells(target(1).Row,"W").Value = _ Cells(target(1).Row,"W").Value + 1 Cells(target(1).Row,"Z").Value = _ Cells(target(1).Row,"Z").Value + _ Target.Value Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Jay" wrote in message ... I need to have this section of code modified to include a wide range of W3:w6000, Z3:Z6000, AH3:AH6000. How would one accomplish that? See code that follows: --------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally If Target.Address < "$AH$3" Then Exit Sub Application.EnableEvents = False Range("W3").Value = Range("W3").Value + 1 Range("Z3").Value = Range("Z3").Value + Target.Value Application.EnableEvents = True End Sub ------------------ THanks, |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify this a bit
Thanks Tom:
Now I've just bee informed by management that they'd like to add two additional columns to the mix that will do the same as two prior ones. Only now they want to enter values to column AI, incement column "AA" by that value and add 1 to column "X", see below. This should be it, I hope! Thanks, for the assistance. ---------- Private Sub Worksheet_Change(ByVal Target As Range, ByVal Target1 As Range1) 'Plugging the starting value in column Z and increasing it incrementally Set rng = Range("AH3:AH6000") If Intersect(Target, rng) Is Nothing Then Exit Sub Application.EnableEvents = False Cells(Target(1).Row, "W").Value = Cells(Target(1).Row, "W").Value + 1 Cells(Target(1).Row, "Z").Value = Cells(Target(1).Row, "Z").Value + Target.Value Application.EnableEvents = True Set rng1 = Range1("AI3:AI6000") If Intersect(Target1, rng1) Is Nothing Then Exit Sub Application.EnableEvents = False Cells(Target(1).Row, "X").Value = Cells(Target1(1).Row, "X").Value + 1 Cells(Target(1).Row, "AA").Value = Cells(Target1(1).Row, "AA").Value + Target.Value Application.EnableEvents = True End Sub --------- "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally set rng = Range("AH3:AH6000") If Intersect(target,rng) is nothing Then Exit Sub Application.EnableEvents = False Cells(target(1).Row,"W").Value = _ Cells(target(1).Row,"W").Value + 1 Cells(target(1).Row,"Z").Value = _ Cells(target(1).Row,"Z").Value + _ Target.Value Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Jay" wrote in message ... I need to have this section of code modified to include a wide range of W3:w6000, Z3:Z6000, AH3:AH6000. How would one accomplish that? See code that follows: --------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally If Target.Address < "$AH$3" Then Exit Sub Application.EnableEvents = False Range("W3").Value = Range("W3").Value + 1 Range("Z3").Value = Range("Z3").Value + Target.Value Application.EnableEvents = True End Sub ------------------ THanks, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify this a bit
Hey Tom:
I got it work partialy, I may need a bit more assistance to fix the errors. If I make an addition to column AI it increments both other columns as well. I need to isolate the changes to just the target ones: "X" +1 and "AA" + Value affected by a value change in "AI". --------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AH3:AH6000", "AI3:AI6000")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("AH3:AH6000", "AI3:AI6000")) Range("W" & myCell.Row).Value = Range("W" & myCell.Row).Value + 1 Range("Z" & myCell.Row).Value = Range("Z" & myCell.Row).Value + myCell.Value Range("X" & myCell.Row).Value = Range("X" & myCell.Row).Value + 1 Range("AA" & myCell.Row).Value = Range("AA" & myCell.Row).Value + myCell.Value Next myCell Application.EnableEvents = True End Sub -------------------- "Jay" wrote: Thanks Tom: Now I've just bee informed by management that they'd like to add two additional columns to the mix that will do the same as two prior ones. Only now they want to enter values to column AI, incement column "AA" by that value and add 1 to column "X", see below. This should be it, I hope! Thanks, for the assistance. ---------- Private Sub Worksheet_Change(ByVal Target As Range, ByVal Target1 As Range1) 'Plugging the starting value in column Z and increasing it incrementally Set rng = Range("AH3:AH6000") If Intersect(Target, rng) Is Nothing Then Exit Sub Application.EnableEvents = False Cells(Target(1).Row, "W").Value = Cells(Target(1).Row, "W").Value + 1 Cells(Target(1).Row, "Z").Value = Cells(Target(1).Row, "Z").Value + Target.Value Application.EnableEvents = True Set rng1 = Range1("AI3:AI6000") If Intersect(Target1, rng1) Is Nothing Then Exit Sub Application.EnableEvents = False Cells(Target(1).Row, "X").Value = Cells(Target1(1).Row, "X").Value + 1 Cells(Target(1).Row, "AA").Value = Cells(Target1(1).Row, "AA").Value + Target.Value Application.EnableEvents = True End Sub --------- "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally set rng = Range("AH3:AH6000") If Intersect(target,rng) is nothing Then Exit Sub Application.EnableEvents = False Cells(target(1).Row,"W").Value = _ Cells(target(1).Row,"W").Value + 1 Cells(target(1).Row,"Z").Value = _ Cells(target(1).Row,"Z").Value + _ Target.Value Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Jay" wrote in message ... I need to have this section of code modified to include a wide range of W3:w6000, Z3:Z6000, AH3:AH6000. How would one accomplish that? See code that follows: --------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally If Target.Address < "$AH$3" Then Exit Sub Application.EnableEvents = False Range("W3").Value = Range("W3").Value + 1 Range("Z3").Value = Range("Z3").Value + Target.Value Application.EnableEvents = True End Sub ------------------ THanks, |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify this a bit
Hi Tom:
I got it to work a bit, please check it for errors. Not sure of where it's failing: ------------------ Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AH3:AH6000", "AI3:AI6000")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("AH3:AH6000", "AI3:AI6000")) Range("W" & myCell.Row).Value = Range("W" & myCell.Row).Value + 1 Range("Z" & myCell.Row).Value = Range("Z" & myCell.Row).Value + myCell.Value Range("X" & myCell.Row).Value = Range("X" & myCell.Row).Value + 1 Range("AA" & myCell.Row).Value = Range("AA" & myCell.Row).Value + myCell.Value Next myCell Application.EnableEvents = True End Sub ----------------- "Jay" wrote: Thanks Tom: Now I've just bee informed by management that they'd like to add two additional columns to the mix that will do the same as two prior ones. Only now they want to enter values to column AI, incement column "AA" by that value and add 1 to column "X", see below. This should be it, I hope! Thanks, for the assistance. ---------- Private Sub Worksheet_Change(ByVal Target As Range, ByVal Target1 As Range1) 'Plugging the starting value in column Z and increasing it incrementally Set rng = Range("AH3:AH6000") If Intersect(Target, rng) Is Nothing Then Exit Sub Application.EnableEvents = False Cells(Target(1).Row, "W").Value = Cells(Target(1).Row, "W").Value + 1 Cells(Target(1).Row, "Z").Value = Cells(Target(1).Row, "Z").Value + Target.Value Application.EnableEvents = True Set rng1 = Range1("AI3:AI6000") If Intersect(Target1, rng1) Is Nothing Then Exit Sub Application.EnableEvents = False Cells(Target(1).Row, "X").Value = Cells(Target1(1).Row, "X").Value + 1 Cells(Target(1).Row, "AA").Value = Cells(Target1(1).Row, "AA").Value + Target.Value Application.EnableEvents = True End Sub --------- "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally set rng = Range("AH3:AH6000") If Intersect(target,rng) is nothing Then Exit Sub Application.EnableEvents = False Cells(target(1).Row,"W").Value = _ Cells(target(1).Row,"W").Value + 1 Cells(target(1).Row,"Z").Value = _ Cells(target(1).Row,"Z").Value + _ Target.Value Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Jay" wrote in message ... I need to have this section of code modified to include a wide range of W3:w6000, Z3:Z6000, AH3:AH6000. How would one accomplish that? See code that follows: --------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally If Target.Address < "$AH$3" Then Exit Sub Application.EnableEvents = False Range("W3").Value = Range("W3").Value + 1 Range("Z3").Value = Range("Z3").Value + Target.Value Application.EnableEvents = True End Sub ------------------ THanks, |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modify this a bit
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AH3:AH6000", "AI3:AI6000")) Is Nothing Then Exit Sub On Error goto ErrHandler Application.EnableEvents = False For Each myCell In Intersect(Target, Range("AH3:AH6000", "AI3:AI6000")) if myCell.column = 34 then ' AH Range("W" & myCell.Row).Value = _ Range("W" & myCell.Row).Value + 1 Range("Z" & myCell.Row).Value = _ Range("Z" & myCell.Row).Value + myCell.Value else Range("X" & myCell.Row).Value = _ Range("X" & myCell.Row).Value + 1 Range("AA" & myCell.Row).Value = _ Range("AA" & myCell.Row).Value + myCell.Value End if Next myCell ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Jay" wrote in message ... Hi Tom: I got it to work a bit, please check it for errors. Not sure of where it's failing: ------------------ Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("AH3:AH6000", "AI3:AI6000")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each myCell In Intersect(Target, Range("AH3:AH6000", "AI3:AI6000")) Range("W" & myCell.Row).Value = Range("W" & myCell.Row).Value + 1 Range("Z" & myCell.Row).Value = Range("Z" & myCell.Row).Value + myCell.Value Range("X" & myCell.Row).Value = Range("X" & myCell.Row).Value + 1 Range("AA" & myCell.Row).Value = Range("AA" & myCell.Row).Value + myCell.Value Next myCell Application.EnableEvents = True End Sub ----------------- "Jay" wrote: Thanks Tom: Now I've just bee informed by management that they'd like to add two additional columns to the mix that will do the same as two prior ones. Only now they want to enter values to column AI, incement column "AA" by that value and add 1 to column "X", see below. This should be it, I hope! Thanks, for the assistance. ---------- Private Sub Worksheet_Change(ByVal Target As Range, ByVal Target1 As Range1) 'Plugging the starting value in column Z and increasing it incrementally Set rng = Range("AH3:AH6000") If Intersect(Target, rng) Is Nothing Then Exit Sub Application.EnableEvents = False Cells(Target(1).Row, "W").Value = Cells(Target(1).Row, "W").Value + 1 Cells(Target(1).Row, "Z").Value = Cells(Target(1).Row, "Z").Value + Target.Value Application.EnableEvents = True Set rng1 = Range1("AI3:AI6000") If Intersect(Target1, rng1) Is Nothing Then Exit Sub Application.EnableEvents = False Cells(Target(1).Row, "X").Value = Cells(Target1(1).Row, "X").Value + 1 Cells(Target(1).Row, "AA").Value = Cells(Target1(1).Row, "AA").Value + Target.Value Application.EnableEvents = True End Sub --------- "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally set rng = Range("AH3:AH6000") If Intersect(target,rng) is nothing Then Exit Sub Application.EnableEvents = False Cells(target(1).Row,"W").Value = _ Cells(target(1).Row,"W").Value + 1 Cells(target(1).Row,"Z").Value = _ Cells(target(1).Row,"Z").Value + _ Target.Value Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Jay" wrote in message ... I need to have this section of code modified to include a wide range of W3:w6000, Z3:Z6000, AH3:AH6000. How would one accomplish that? See code that follows: --------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Plugging the starting value in column Z and increasing it incrementally If Target.Address < "$AH$3" Then Exit Sub Application.EnableEvents = False Range("W3").Value = Range("W3").Value + 1 Range("Z3").Value = Range("Z3").Value + Target.Value Application.EnableEvents = True End Sub ------------------ THanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Modify code | Excel Discussion (Misc queries) | |||
| Modify Code | Excel Worksheet Functions | |||
| Modify a UDF please? | Excel Worksheet Functions | |||
| Modify a UDF please? | Excel Worksheet Functions | |||
| How would I modify the following? | Excel Programming | |||
Linear Mode
