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= "D4:D21" "D27:D87"
"D93:D153" "D164:D224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627:D644" "C673:C690" "C696:C713" "C719:C736" "D765:D782"
"D788:D805" "D811:D828" "D834:D851" "D857:D874" "D880:D897" "D903:D920"
"D926:D943" "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" "P4:P21" "P27:P87" "P93:P153" "P164:P224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627:P644"
"O673:O690" "O696:O713" "O719:O736" "P765:P872" "P788:P805" "P811:P828"
"P834:P851" "P857:P874" "P880:P897" "P903:P920" "P926:P943" "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= "D4:D21" "D27:D87", is all in red
and after this entry in the above string, "D27:D87", 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 :)

You need to tie those strings together with the ampersand (&).
str = "example" & "more"


--
http://HelpExcel.com

516-984-0252


"IJ" wrote:

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= "D4:D21" "D27:D87"
"D93:D153" "D164:D224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627:D644" "C673:C690" "C696:C713" "C719:C736" "D765:D782"
"D788:D805" "D811:D828" "D834:D851" "D857:D874" "D880:D897" "D903:D920"
"D926:D943" "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" "P4:P21" "P27:P87" "P93:P153" "P164:P224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627:P644"
"O673:O690" "O696:O713" "O719:O736" "P765:P872" "P788:P805" "P811:P828"
"P834:P851" "P857:P874" "P880:P897" "P903:P920" "P926:P943" "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= "D4:D21" "D27:D87", is all in red
and after this entry in the above string, "D27:D87", 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 :)

"D4:D21" "D27:D87" is not a single string, so the assignment will fail right
there.

And sometimes working with lots of addresses can cause problems. I'd do
something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim WS_RANGEALL As Range

Const WS_RANGE1 As String = "D4:D21,D27:D87," & _
"D93:D153,D164:D224,C274:C334,C340:C400,C406:C466, C472:C532," & _
"C538:C598,D627:D644,C673:C690,C696:C713,C719:C736 ,D765:D782," & _
"D788:D805,D811:D828,D834:D851,D857:D874,D880:D897 ,D903:D920"

Const WS_RANGE2 As String = _
"D926:D943,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"

Const WS_RANGE3 As String = _
"J880:J897,J903:J920,J926:J943,P4:P21,P27:P87,P93: P153,P164:P224," & _
"O274:O334,O340:O400,O406:O466,O472:O532,O538:O598 ,P627:P644," & _
"O673:O690,O696:O713,O719:O736,P765:P872,P788:P805 ,P811:P828"

Const WS_RANGE4 As String = _
"P834:P851,P857:P874,P880:P897,P903:P920,P926:P943 ,V4:V21," & _
"V27:V87,V93:V153,V164:V224,U274:U344,U340:U400,U4 06:U466," & _
"U472:U532,U538:U598,V627:V644,U673:U690,U696:U713 ,U719:U736"

Const WS_RANGE5 As String = _
"V765:V872,V788:V805,V811:V828,V834:V851,V857:V874 ,V880:V897," & _
"V903:V920,V926:V943"

Set WS_RANGEALL = Union(Me.Range(WS_RANGE1), _
Me.Range(WS_RANGE2), Me.Range(WS_RANGE3), _
Me.Range(WS_RANGE4), Me.Range(WS_RANGE5))

If Target.Cells.Count 1 Then Exit Sub

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, WS_RANGEALL) Is Nothing Then
With Target
If IsNumeric(.Value) Then
If .Value 0 Then
.Value = .Value * -1
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


IJ wrote:

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= "D4:D21" "D27:D87"
"D93:D153" "D164:D224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627:D644" "C673:C690" "C696:C713" "C719:C736" "D765:D782"
"D788:D805" "D811:D828" "D834:D851" "D857:D874" "D880:D897" "D903:D920"
"D926:D943" "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" "P4:P21" "P27:P87" "P93:P153" "P164:P224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627:P644"
"O673:O690" "O696:O713" "O719:O736" "P765:P872" "P788:P805" "P811:P828"
"P834:P851" "P857:P874" "P880:P897" "P903:P920" "P926:P943" "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= "D4:D21" "D27:D87", is all in red
and after this entry in the above string, "D27:D87", 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 :)



--

Dave Peterson

Hi galimi...

Thanks !!! :)

I will give your suggestion a try ! :)

Take care and KIP

IJ :)

"galimi" wrote:

You need to tie those strings together with the ampersand (&).
str = "example" & "more"


--
http://HelpExcel.com

516-984-0252


"IJ" wrote:

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= "D4:D21" "D27:D87"
"D93:D153" "D164:D224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627:D644" "C673:C690" "C696:C713" "C719:C736" "D765:D782"
"D788:D805" "D811:D828" "D834:D851" "D857:D874" "D880:D897" "D903:D920"
"D926:D943" "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" "P4:P21" "P27:P87" "P93:P153" "P164:P224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627:P644"
"O673:O690" "O696:O713" "O719:O736" "P765:P872" "P788:P805" "P811:P828"
"P834:P851" "P857:P874" "P880:P897" "P903:P920" "P926:P943" "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= "D4:D21" "D27:D87", is all in red
and after this entry in the above string, "D27:D87", 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 :)

Hi Dave...

Thanks !!!! :)

I will give this a try and see how it goes !

Take care and KIP

IJ :)


"Dave Peterson" wrote:

"D4:D21" "D27:D87" is not a single string, so the assignment will fail right
there.

And sometimes working with lots of addresses can cause problems. I'd do
something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim WS_RANGEALL As Range

Const WS_RANGE1 As String = "D4:D21,D27:D87," & _
"D93:D153,D164:D224,C274:C334,C340:C400,C406:C466, C472:C532," & _
"C538:C598,D627:D644,C673:C690,C696:C713,C719:C736 ,D765:D782," & _
"D788:D805,D811:D828,D834:D851,D857:D874,D880:D897 ,D903:D920"

Const WS_RANGE2 As String = _
"D926:D943,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"

Const WS_RANGE3 As String = _
"J880:J897,J903:J920,J926:J943,P4:P21,P27:P87,P93: P153,P164:P224," & _
"O274:O334,O340:O400,O406:O466,O472:O532,O538:O598 ,P627:P644," & _
"O673:O690,O696:O713,O719:O736,P765:P872,P788:P805 ,P811:P828"

Const WS_RANGE4 As String = _
"P834:P851,P857:P874,P880:P897,P903:P920,P926:P943 ,V4:V21," & _
"V27:V87,V93:V153,V164:V224,U274:U344,U340:U400,U4 06:U466," & _
"U472:U532,U538:U598,V627:V644,U673:U690,U696:U713 ,U719:U736"

Const WS_RANGE5 As String = _
"V765:V872,V788:V805,V811:V828,V834:V851,V857:V874 ,V880:V897," & _
"V903:V920,V926:V943"

Set WS_RANGEALL = Union(Me.Range(WS_RANGE1), _
Me.Range(WS_RANGE2), Me.Range(WS_RANGE3), _
Me.Range(WS_RANGE4), Me.Range(WS_RANGE5))

If Target.Cells.Count 1 Then Exit Sub

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, WS_RANGEALL) Is Nothing Then
With Target
If IsNumeric(.Value) Then
If .Value 0 Then
.Value = .Value * -1
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


IJ wrote:

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= "D4:D21" "D27:D87"
"D93:D153" "D164:D224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627:D644" "C673:C690" "C696:C713" "C719:C736" "D765:D782"
"D788:D805" "D811:D828" "D834:D851" "D857:D874" "D880:D897" "D903:D920"
"D926:D943" "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" "P4:P21" "P27:P87" "P93:P153" "P164:P224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627:P644"
"O673:O690" "O696:O713" "O719:O736" "P765:P872" "P788:P805" "P811:P828"
"P834:P851" "P857:P874" "P880:P897" "P903:P920" "P926:P943" "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= "D4:D21" "D27:D87", is all in red
and after this entry in the above string, "D27:D87", 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 :)



--

Dave Peterson

Oh dear.

You are trying to build one character string.
Because it is so long you will have to do it in bits and join them
together., which you do like this (as has been posted)

string1 = string2 & string3

You can use considerably bigger bits.
A range can be built from different bits separated by a comma
If you have to, or prefer to, use more than one line to build your string
you have to put a space followed by an underline at the end of the first
line.

This example is tested and works.

Const WS_RANGE As String= "D4:D21,D21:D14" _
& ",C38:C59,D6:D8,C17:C21"

Happy glueing.



"IJ" wrote in message
...
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= "D4:D21" "D27:D87"
"D93:D153" "D164:D224" "C274:C334" "C340:C400" "C406:C466" "C472:C532"
"C538:C598" "D627:D644" "C673:C690" "C696:C713" "C719:C736" "D765:D782"
"D788:D805" "D811:D828" "D834:D851" "D857:D874" "D880:D897" "D903:D920"
"D926:D943" "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" "P4:P21" "P27:P87" "P93:P153"
"P164:P224"
"O274:O334" "O340:O400" "O406:O466" "O472:O532" "O538:O598" "P627:P644"
"O673:O690" "O696:O713" "O719:O736" "P765:P872" "P788:P805" "P811:P828"
"P834:P851" "P857:P874" "P880:P897" "P903:P920" "P926:P943" "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= "D4:D21" "D27:D87", is all in red
and after this entry in the above string, "D27:D87", 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 :)