Worksheet Change by Value problem

G

Guest

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?
 
J

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
 
G

Guest

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 said:
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 said:
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?
 
J

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top