Hello...I have a code that keeps getting a runtime 1004 error. I beleive it
is because the string is too long. Anyone have any suggestions on how to
shorten the string or expand the default range? Here is my code....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String =
"M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12,M13,M14,M15,M 16,M17,M18,M19,M20,M21,M22,M23,M24,M25,M26,M27,M28 ,M29,M30,M31,M32,M33,M34,M35,M36,M37,M38,M39,M40,M 41,M42,M43,M44,M45,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11 ,N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23,N 24,N25,N26,N27,N28,N29,N30,N31,N32,N33,N34,N35,N36 ,N37,N38,N39,N40,N41,N42,N43,N44,N45"
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range(sINPUTS), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub

--
Randy Street
Rancho Cucamonga, CA

Not quite sure what you are doing but try this??

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set myrng = Range("m2:m45,n2:n45")
'or more simply
'Set myrng = Range("m2:n45")

With Target
If .Count 1 Then Exit Sub
If Not Intersect(Target, myrng) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Randy" wrote in message
...
Hello...I have a code that keeps getting a runtime 1004 error. I beleive
it
is because the string is too long. Anyone have any suggestions on how to
shorten the string or expand the default range? Here is my code....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String =
"M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12,M13,M14,M15,M 16,M17,M18,M19,M20,M21,M22,M23,M24,M25,M26,M27,M28 ,M29,M30,M31,M32,M33,M34,M35,M36,M37,M38,M39,M40,M 41,M42,M43,M44,M45,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11 ,N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23,N 24,N25,N26,N27,N28,N29,N30,N31,N32,N33,N34,N35,N36 ,N37,N38,N39,N40,N41,N42,N43,N44,N45"
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range(sINPUTS), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub

--
Randy Street
Rancho Cucamonga, CA

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String = "M2:M45, N2:N45"
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range(sINPUTS), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Fri, 28 Mar 2008 15:39:00 -0700, Randy
wrote:

Hello...I have a code that keeps getting a runtime 1004 error. I beleive it
is because the string is too long. Anyone have any suggestions on how to
shorten the string or expand the default range? Here is my code....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String =
"M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12,M13,M14,M15, M16,M17,M18,M19,M20,M21,M22,M23,M24,M25,M26,M27,M2 8,M29,M30,M31,M32,M33,M34,M35,M36,M37,M38,M39,M40, M41,M42,M43,M44,M45,N2,N3,N4,N5,N6,N7,N8,N9,N10,N1 1,N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23, N24,N25,N26,N27,N28,N29,N30,N31,N32,N33,N34,N35,N3 6,N37,N38,N39,N40,N41,N42,N43,N44,N45"
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range(sINPUTS), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub

Thanks guys....Gord I tried your version prior but for some reason didn;t
work...this time it did! I thank you for your assistance!
--
Randy Street
Rancho Cucamonga, CA


"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String = "M2:M45, N2:N45"
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range(sINPUTS), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Fri, 28 Mar 2008 15:39:00 -0700, Randy
wrote:

Hello...I have a code that keeps getting a runtime 1004 error. I beleive it
is because the string is too long. Anyone have any suggestions on how to
shorten the string or expand the default range? Here is my code....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String =
"M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12,M13,M14,M15, M16,M17,M18,M19,M20,M21,M22,M23,M24,M25,M26,M27,M2 8,M29,M30,M31,M32,M33,M34,M35,M36,M37,M38,M39,M40, M41,M42,M43,M44,M45,N2,N3,N4,N5,N6,N7,N8,N9,N10,N1 1,N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23, N24,N25,N26,N27,N28,N29,N30,N31,N32,N33,N34,N35,N3 6,N37,N38,N39,N40,N41,N42,N43,N44,N45"
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range(sINPUTS), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub