Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Change by Value problem
I have the following macro that I use on a data report. The macro adds
calculations to each row for the length of the data. Sub SelRates() ' ' SelRates Macro ' Macro recorded 2/10/2007 by me ' Columns("A:A").ColumnWidth = 11.43 Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V ,X:X").Select Range("X1").Activate Selection.ColumnWidth = 0.75 Range("A7").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.AutoFilter Dim LastRow As Long With Worksheets("Labour") LastRow = .Cells(.Rows.Count, "Y").End(xlUp).Row '.Range("Z8:Z" & LastRow).FormulaR1C1 = "=INDEX(Rates!C[-25]:C[-15],MATCH(Labour!RC[-13],Rates!C[-24],0),4)" .Range("AA8:AA" & LastRow).FormulaR1C1 = "=RC[-8]*RC[-1]" End With Range("U6").Select ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[4994]C)" Selection.Copy Range("AA6").Select ActiveSheet.Paste Sheets("Labour").Select Cells.Select Selection.Replace What:="Normal", Replacement:="Norm", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("Z:AA,U:U").Select Range("U1").Activate Selection.Style = "Comma" Range("AA6").Select Sheets("Summary").Select Range("A5").Select End Sub It runs okay when run from the macro. When I run the macro from Private Sub Worksheet_SelectionChange(ByVal Target As Range) it loops. Obviously, it keeps running each time it enters the new data. Does anyone have an additon to the code above that stops the WITH statement at the last line of data? -- Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Change by Value problem
One way:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False 'your code here ErrorHandler: Application.EnableEvents = True End Sub In article , Jim G wrote: I have the following macro that I use on a data report. The macro adds calculations to each row for the length of the data. Sub SelRates() ' ' SelRates Macro ' Macro recorded 2/10/2007 by me ' Columns("A:A").ColumnWidth = 11.43 Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V ,X:X").Select Range("X1").Activate Selection.ColumnWidth = 0.75 Range("A7").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.AutoFilter Dim LastRow As Long With Worksheets("Labour") LastRow = .Cells(.Rows.Count, "Y").End(xlUp).Row '.Range("Z8:Z" & LastRow).FormulaR1C1 = "=INDEX(Rates!C[-25]:C[-15],MATCH(Labour!RC[-13],Rates!C[-24],0),4)" .Range("AA8:AA" & LastRow).FormulaR1C1 = "=RC[-8]*RC[-1]" End With Range("U6").Select ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[4994]C)" Selection.Copy Range("AA6").Select ActiveSheet.Paste Sheets("Labour").Select Cells.Select Selection.Replace What:="Normal", Replacement:="Norm", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("Z:AA,U:U").Select Range("U1").Activate Selection.Style = "Comma" Range("AA6").Select Sheets("Summary").Select Range("A5").Select End Sub It runs okay when run from the macro. When I run the macro from Private Sub Worksheet_SelectionChange(ByVal Target As Range) it loops. Obviously, it keeps running each time it enters the new data. Does anyone have an additon to the code above that stops the WITH statement at the last line of data? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Change by Value problem
Perfect solution. Thanks for your help. Should I routinely include this in
all my macros? BTW, I meant "Private Sub Worksheet_Change(ByVal Target As Range)" and not "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" as originally posted. -- Jim "JE McGimpsey" wrote: One way: Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False 'your code here ErrorHandler: Application.EnableEvents = True End Sub In article , Jim G wrote: I have the following macro that I use on a data report. The macro adds calculations to each row for the length of the data. Sub SelRates() ' ' SelRates Macro ' Macro recorded 2/10/2007 by me ' Columns("A:A").ColumnWidth = 11.43 Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N,P:P,R:R,T:T,V:V ,X:X").Select Range("X1").Activate Selection.ColumnWidth = 0.75 Range("A7").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.AutoFilter Dim LastRow As Long With Worksheets("Labour") LastRow = .Cells(.Rows.Count, "Y").End(xlUp).Row '.Range("Z8:Z" & LastRow).FormulaR1C1 = "=INDEX(Rates!C[-25]:C[-15],MATCH(Labour!RC[-13],Rates!C[-24],0),4)" .Range("AA8:AA" & LastRow).FormulaR1C1 = "=RC[-8]*RC[-1]" End With Range("U6").Select ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[4994]C)" Selection.Copy Range("AA6").Select ActiveSheet.Paste Sheets("Labour").Select Cells.Select Selection.Replace What:="Normal", Replacement:="Norm", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("Z:AA,U:U").Select Range("U1").Activate Selection.Style = "Comma" Range("AA6").Select Sheets("Summary").Select Range("A5").Select End Sub It runs okay when run from the macro. When I run the macro from Private Sub Worksheet_SelectionChange(ByVal Target As Range) it loops. Obviously, it keeps running each time it enters the new data. Does anyone have an additon to the code above that stops the WITH statement at the last line of data? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Change by Value problem
You should use it any time you don't want events to fire while you're
running your macro. In most macros it isn't necessary or desirable, but in some cases, especially within event macros, it may be necessary to prevent undesirable effects (like a cell change within Worksheet_Change that causes it to be invoked, etc., until XL runs out of stack space). In article , Jim G wrote: Perfect solution. Thanks for your help. Should I routinely include this in all my macros? BTW, I meant "Private Sub Worksheet_Change(ByVal Target As Range)" and not "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" as originally posted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with concatination result. Last digit change to 0 | Excel Worksheet Functions | |||
Change of auto backup directory has created problem | Excel Discussion (Misc queries) | |||
Problem calculating percent change | Excel Worksheet Functions | |||
Worksheet Problem | Excel Worksheet Functions | |||
Sorting problem, sums change | Excel Discussion (Misc queries) |