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

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

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