Help with Code

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

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,M28,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,N11,N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23,N24,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
 
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
 
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
 
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!
 
Back
Top