Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code with formula interfering in worksheet change event
Hello,
When this code is in the worksheet change event, it restricts the function of other code in worksheet change. Why? Is it about the placement of the Exit Sub? All this code does is carries formulas in columns 3 and 10 down to new rows when inserted manually. If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub On Error Goto CleanUp: With Target If .Value < "" Then Application.EnableEvents = False .Offset(0, 3).FormulaR1C1 = "=RC[-1]=R2C3" .Offset(0, 10).FormulaR1C1 = "=RC[-1]=R2C10" .Offset(0, 2).FormulaR1C1 = "=if(rc[-2]<"""",rc[-1],"""")" .Offset(0, 9).FormulaR1C1 = "=if(rc[-8]<0,if(rc[-7]<rc[-8],rc[-9]&"", ""&left(rc[-7],2),rc[-9]&"", ""&left(rc[-8],1)),"""")" End If End With CleanUp: Application.EnableEvents = True Also, could you help me with syntax for another vb formula that essentially is this: =OFFSET(Column10sHeading,0,246) which just sets the last column equal to column 10. Thanks greatly, Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code with formula interfering in worksheet change event
Which other code?
And what do you mean by "restrict"? "Arnold" wrote in message oups.com... Hello, When this code is in the worksheet change event, it restricts the function of other code in worksheet change. Why? Is it about the placement of the Exit Sub? All this code does is carries formulas in columns 3 and 10 down to new rows when inserted manually. If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub On Error Goto CleanUp: With Target If .Value < "" Then Application.EnableEvents = False .Offset(0, 3).FormulaR1C1 = "=RC[-1]=R2C3" .Offset(0, 10).FormulaR1C1 = "=RC[-1]=R2C10" .Offset(0, 2).FormulaR1C1 = "=if(rc[-2]<"""",rc[-1],"""")" .Offset(0, 9).FormulaR1C1 = "=if(rc[-8]<0,if(rc[-7]<rc[-8],rc[-9]&"", ""&left(rc[-7],2),rc[-9]&"", ""&left(rc[-8],1)),"""")" End If End With CleanUp: Application.EnableEvents = True Also, could you help me with syntax for another vb formula that essentially is this: =OFFSET(Column10sHeading,0,246) which just sets the last column equal to column 10. Thanks greatly, Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code with formula interfering in worksheet change event
Here's the other code. By restrict, I meant that the code below
doesn't work when the above code is in the sheet event. The code below puts the value of cells in the last column (range is AbbNames) equal to the cells in column 10 (range Abbreviated), and then updates any changed values on another sheet, Schedules. On Error GoTo errHandler If Target.Count 1 Then Exit Sub On Error Resume Next Set rng = Target.Dependents On Error GoTo 0 If Not rng Is Nothing Then Set rng1 = Union(rng, Target) Else Set rng1 = Target End If On Error GoTo errHandler If Not Application.Intersect(rng1, _ Application.Range("Abbreviated")) Is Nothing Then strOld = Target.EntireRow.Range("IV1").Value strNew = Target.EntireRow.Range("J1").Value Application.EnableEvents = False Application.ScreenUpdating = False Application.Range("AbbNames").Value = _ Application.Range("Abbreviated").Value End If Set wsData = Worksheets("Schedules") On Error GoTo errHandler Set myRange = wsData.Range("Students") For Each mycell In myRange If mycell.Value = "" Then Else If strOld < strNew And strOld < "" Then Application.EnableEvents = False Application.ScreenUpdating = False wsData.Range("Students").Replace What:=strOld, _ Replacement:=strNew, LookAt:=xlPart, _ SearchOrder:=xlByRows End If End If Next Application.Range("AbbNames").Value = _ Application.Range("Abbreviated").Value errHandler: Application.EnableEvents = True Application.ScreenUpdating = True Application.Calculation = xlAutomatic If Err.Number < 0 Then _ MsgBox "Error occured " & Err.Number & vbNewLine & _ Err.Description exitHandler: Application.EnableEvents = True Application.ScreenUpdating = True Application.Calculation = xlAutomatic Exit Sub End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
Enable/Disable Worksheet Change Event code | Excel Programming | |||
Copy Sheets minus Worksheet Change Event code & Macro Buttons | Excel Programming |