G
Guest
Hello,
I am trying to create a worksheet with specific columns that have a negative
value.
I entered the following into the Visual Basic Code page:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String= "D421" "D2787"
"D93153" "D164224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627644" "C673:C690" "C696:C713" "C719:C736" "D765782"
"D788805" "D811828" "D834851" "D857874" "D880897" "D903920"
"D926943" "J4:J21" "J27:J87" "J93:J153" "J164:J224" "I274:I334" "I340"I400"
"I406:I466" "I472:I532" "I538":I598" "J627:J644" "I673:I690" "I696:I713"
"I719:I736" "J765:J782" "J788:J805" "J811:J828" "J834:J851" "J857:J874"
"J880:J897" "J903:J920" "J926:J943" "P421" "P2787" "P93153" "P164224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627644"
"O673:O690" "O696:O713" "O719:O736" "P765872" "P788805" "P811828"
"P834851" "P857874" "P880897" "P903920" "P926943" "V4:V21"
"V27:V87" "V93:V153" "V164:V224" "U274:U344" "U340:U400" "U406:U466"
"U472:U532" "U538:U598" "V627:V644" "U673:U690" "U696:U713" "U719:U736"
"V765:V872" "V788:V805" "V811:V828" "V834:V851" "V857:V874" "V880:V897"
"V903:V920" "V926:V943"
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value * -1
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
And the line "Const WS_RANGE As String= "D421" "D2787", is all in red
and after this entry in the above string, "D2787", I received the
following error code:
"Micrsoft Visual Basic Compile error: Expected: end of statement"
Would anyone know why? And if so, is there a way to correct this error and
if so, how?
Any help or information anyone may be able to provide will be greatly
appreciate...Thanks !!!!
Take care and KIP
IJ
I am trying to create a worksheet with specific columns that have a negative
value.
I entered the following into the Visual Basic Code page:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String= "D421" "D2787"
"D93153" "D164224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627644" "C673:C690" "C696:C713" "C719:C736" "D765782"
"D788805" "D811828" "D834851" "D857874" "D880897" "D903920"
"D926943" "J4:J21" "J27:J87" "J93:J153" "J164:J224" "I274:I334" "I340"I400"
"I406:I466" "I472:I532" "I538":I598" "J627:J644" "I673:I690" "I696:I713"
"I719:I736" "J765:J782" "J788:J805" "J811:J828" "J834:J851" "J857:J874"
"J880:J897" "J903:J920" "J926:J943" "P421" "P2787" "P93153" "P164224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627644"
"O673:O690" "O696:O713" "O719:O736" "P765872" "P788805" "P811828"
"P834851" "P857874" "P880897" "P903920" "P926943" "V4:V21"
"V27:V87" "V93:V153" "V164:V224" "U274:U344" "U340:U400" "U406:U466"
"U472:U532" "U538:U598" "V627:V644" "U673:U690" "U696:U713" "U719:U736"
"V765:V872" "V788:V805" "V811:V828" "V834:V851" "V857:V874" "V880:V897"
"V903:V920" "V926:V943"
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value * -1
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
And the line "Const WS_RANGE As String= "D421" "D2787", is all in red
and after this entry in the above string, "D2787", I received the
following error code:
"Micrsoft Visual Basic Compile error: Expected: end of statement"
Would anyone know why? And if so, is there a way to correct this error and
if so, how?
Any help or information anyone may be able to provide will be greatly
appreciate...Thanks !!!!
Take care and KIP
IJ