Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conflict of two macros?
Hi all,
I have the below two codes in my worksheet, the first one is run using a button and the second one is an automatic macro to enter fixed dates. There seems to be a conflict, as each time I use the button to add a row, I get an error, and when I go to debug it highlights this row in the second macro -- If Target.Value = "" Then Is there a solution? I can't see why there is an error, as both are separate codes? Any help will be appreciated! Thanks! ********************* ***The first code: Private Sub CommandButton1_Click() For i = 1 To 10000 If ActiveSheet.Range("C" & i).Value = "BLENDING FACILITIES" Then Range("C" & (i + 1) & ":" & "AN" & (i + 1)).Select Selection.Copy Selection.Insert Shift:=xlDown For k = 1 To 40 If ActiveSheet.Cells(i + 1, k).HasFormula = False Then ActiveSheet.Cells(i + 1, k).ClearContents End If Next Exit For End If Next End Sub ***The second code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const ColumnsToCheck As String = "C:C,F:F,I:I,L:L,O:O,R:R,U:U,X:X,AA:AA,AD:AD,AG:AG ,AJ:AJ" If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then If Target.Value = "" Then Target.Offset(0, 1).Value = "-" Else Target.Offset(0, 1).Value = Now() End If End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conflict of two macros?
On Mon, 15 Feb 2010 12:57:05 -0800 (PST), "Shivam.Shah"
wrote: Hi all, I have the below two codes in my worksheet, the first one is run using a button and the second one is an automatic macro to enter fixed dates. There seems to be a conflict, as each time I use the button to add a row, I get an error, and when I go to debug it highlights this row in the second macro -- If Target.Value = "" Then Is there a solution? I can't see why there is an error, as both are separate codes? Any help will be appreciated! Thanks! ********************* ***The first code: Private Sub CommandButton1_Click() For i = 1 To 10000 If ActiveSheet.Range("C" & i).Value = "BLENDING FACILITIES" Then Range("C" & (i + 1) & ":" & "AN" & (i + 1)).Select Selection.Copy Selection.Insert Shift:=xlDown For k = 1 To 40 If ActiveSheet.Cells(i + 1, k).HasFormula = False Then ActiveSheet.Cells(i + 1, k).ClearContents End If Next Exit For End If Next End Sub ***The second code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const ColumnsToCheck As String = "C:C,F:F,I:I,L:L,O:O,R:R,U:U,X:X,AA:AA,AD:AD,AG:A G,AJ:AJ" If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then If Target.Value = "" Then Target.Offset(0, 1).Value = "-" Else Target.Offset(0, 1).Value = Now() End If End If End Sub See suggestion at your other post in other forum. --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conflict of two macros?
Thanks!
On Feb 15, 4:14*pm, Ron Rosenfeld wrote: On Mon, 15 Feb 2010 12:57:05 -0800 (PST), "Shivam.Shah" wrote: Hi all, I have the below two codes in my worksheet, the first one is run using a button and the second one is an automatic macro to enter fixed dates. There seems to be a conflict, as each time I use the button to add a row, I get an error, and when I go to debug it highlights this row in the second macro -- If Target.Value = "" Then Is there a solution? I can't see why there is an error, as both are separate codes? Any help will be appreciated! Thanks! ********************* ***The first code: Private Sub CommandButton1_Click() * *For i = 1 To 10000 * * * *If ActiveSheet.Range("C" & i).Value = "BLENDING FACILITIES" Then * * * * * *Range("C" & (i + 1) & ":" & "AN" & (i + 1)).Select * * * * * *Selection.Copy * * * * * *Selection.Insert Shift:=xlDown * * * * * *For k = 1 To 40 * * * * * * * *If ActiveSheet.Cells(i + 1, k).HasFormula = False Then * * * * * * * * * *ActiveSheet.Cells(i + 1, k).ClearContents * * * * * * * *End If * * * * * *Next * * * * * *Exit For * * * *End If * *Next End Sub ***The second code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const ColumnsToCheck As String = "C:C,F:F,I:I,L:L,O:O,R:R,U:U,X:X,AA:AA,AD:AD,AG:A G,AJ:AJ" *If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then * If Target.Value = "" Then * *Target.Offset(0, 1).Value = "-" * *Else * * Target.Offset(0, 1).Value = Now() * *End If *End If End Sub See suggestion at your other post in other forum. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conflict Between Two Add-Ins | Excel Programming | |||
Name conflict | Excel Discussion (Misc queries) | |||
Name Conflict | Excel Discussion (Misc queries) | |||
Conflict | Excel Programming | |||
Name conflict | Excel Programming |