Worksheet Change by Value problem

[Jim G]

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,F:F,H:H,J:J,L:L,N:N,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?

 

[JE McGimpsey]

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

 

[Jim G]

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

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

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,F:F,H:H,J:J,L:L,N:N,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?

 

[JE McGimpsey]

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).