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