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