Hi, i have two macro codes and they both are
Private Sub Worksheet_Change(ByVal Target As Range). I want to mix
them and want to make one code. Please see the Macros belw

Macro 1:-


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B25:D62,K25:K62"


On Error GoTo ws_exit
Application.EnableEvents = False


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If


End If
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


Macro 2:-


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("F25:F62")
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
budget = budget + c.Value
End If
End If
Next c
If Target.Value < "" Then
Target.Offset(0, 5).Value = budget
Else
Target.Offset(0, 5).Value = ""
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Please can anybody able to tell me that how can i make one macro of
two macros as i cant have two macro in one sheet which starts from
Private Sub Worksheet_Change(ByVal Target As Range)
some one send me the sultion (please see below) but its not working




Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "B25:D62,K25:K62"
Const WS_RANGE2 As String = "F25:F62"


On Error GoTo ws_exit
Application.EnableEvents = False


If Target.Cells.Count 1 Then Exit Sub


If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match( _
Me.Cells(.Row, "O").Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
End If


End If
End With
ElseIf Not Intersect(Target, Range(WS_RANGE2)) Is Nothing Then
If IsNumeric(Target) Then
With Target
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup( _
.Offset(0, 8).Value, Range("AB1:AC9995"), 2,
False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = .Offset(0, 9).Value
Then
budget = budget + c.Value
End If
End If
Next c
.Offset(0, 5).Value = IIf(.Value < "", _
budget, .Offset(0, 5).Value = "")
On Error GoTo 0
End With
End If
End If


ws_exit:
Application.EnableEvents = True
End Sub

The easiest way is to just give them names, make one say sub Macro1() and the
other sub macro2(), better names would help:) then just call them from the
worksheet change, so
Private Sub Worksheet_Change(ByVal Target As Range)
macro1
macro2
End sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"K" wrote:

Hi, i have two macro codes and they both are
Private Sub Worksheet_Change(ByVal Target As Range). I want to mix
them and want to make one code. Please see the Macros belw

Macro 1:-


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B25:D62,K25:K62"


On Error GoTo ws_exit
Application.EnableEvents = False


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If


End If
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


Macro 2:-


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("F25:F62")
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
budget = budget + c.Value
End If
End If
Next c
If Target.Value < "" Then
Target.Offset(0, 5).Value = budget
Else
Target.Offset(0, 5).Value = ""
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Please can anybody able to tell me that how can i make one macro of
two macros as i cant have two macro in one sheet which starts from
Private Sub Worksheet_Change(ByVal Target As Range)
some one send me the sultion (please see below) but its not working




Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "B25:D62,K25:K62"
Const WS_RANGE2 As String = "F25:F62"


On Error GoTo ws_exit
Application.EnableEvents = False


If Target.Cells.Count 1 Then Exit Sub


If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match( _
Me.Cells(.Row, "O").Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
End If


End If
End With
ElseIf Not Intersect(Target, Range(WS_RANGE2)) Is Nothing Then
If IsNumeric(Target) Then
With Target
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup( _
.Offset(0, 8).Value, Range("AB1:AC9995"), 2,
False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = .Offset(0, 9).Value
Then
budget = budget + c.Value
End If
End If
Next c
.Offset(0, 5).Value = IIf(.Value < "", _
budget, .Offset(0, 5).Value = "")
On Error GoTo 0
End With
End If
End If


ws_exit:
Application.EnableEvents = True
End Sub

On Dec 31, 4:11*pm, John Bundy (remove) wrote:
The easiest way is to just give them names, make one say sub Macro1() and the
other sub macro2(), better names would help:) then just call them from the
worksheet change, so
Private Sub Worksheet_Change(ByVal Target As Range)
macro1
macro2
End sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.



"K" wrote:
Hi, i have two macro codes and they both are
Private Sub Worksheet_Change(ByVal Target As Range). *I want to mix
them and want to make one code. *Please see the Macros belw

Macro 1:-

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B25:D62,K25:K62"

* * On Error GoTo ws_exit
* * Application.EnableEvents = False

* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * * With Target
* * * * * * If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * * Me.Cells(.Row, "C").Value < "" Then
* * * * * * * * If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
* * * * * * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * * * End If
* * * * * * * * If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * * MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * End If

* * * * * * End If
* * * * End With
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub

Macro 2:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("F25:F62")
* *If Target.Cells.Count 1 Then Exit Sub
* * If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
* * * * If IsNumeric(Target) Then
* * * * * * On Error Resume Next
* * * * * * Application.EnableEvents = False
* * * * * * budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
* * If c.Address < Target.Address Then
* * * * If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
* * * * * *budget = budget + c.Value
* * * * End If
* * End If
Next c
* * * * * *If Target.Value < "" Then
* * * * * * * * Target.Offset(0, 5).Value = budget
* * * * * * Else
* * * * * *Target.Offset(0, 5).Value = ""
* * * * * *End If
* * * * * * * * Application.EnableEvents = True
* * * * * * On Error GoTo 0
* * * * End If
* * End If
End Sub

Please can anybody able to tell me that how can i make one macro of
two macros as i cant have two macro in one sheet which starts from
Private Sub Worksheet_Change(ByVal Target As Range)
some one send me the sultion (please see below) but its not working

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "B25:D62,K25:K62"
Const WS_RANGE2 As String = "F25:F62"

* * On Error GoTo ws_exit
* * Application.EnableEvents = False

* * If Target.Cells.Count 1 Then Exit Sub

* * If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
* * * * With Target
* * * * * * If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * * Me.Cells(.Row, "C").Value < "" Then
* * * * * * * * If IsError(Application.Match( _
* * * * * * * * * * Me.Cells(.Row, "O").Value, Columns(27), 0)) Then
* * * * * * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
* * * * * * * * End If
* * * * * * * * If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * * MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
* * * * * * End If

* * * * * * End If
* * * * End With
* * ElseIf Not Intersect(Target, Range(WS_RANGE2)) Is Nothing Then
* * * * If IsNumeric(Target) Then
* * * * * * With Target
* * * * * * * * *On Error Resume Next
* * * * * * * * *Application.EnableEvents = False
* * * * * * * * *budget = WorksheetFunction.VLookup( _
* * * * * * * * * * .Offset(0, 8).Value, Range("AB1:AC9995"), 2,
False)
* * * * * * * * *For Each c In MyRange
* * * * * * * * * * *If c.Address < Target.Address Then
* * * * * * * * * * * * *If c.Offset(0, 9).Value = .Offset(0, 9).Value
Then
* * * * * * * * * * * * * * budget = budget + c.Value
* * * * * * * * * * * * *End If
* * * * * * * * * * *End If
* * * * * * * * *Next c
* * * * * * * * *.Offset(0, 5).Value = IIf(.Value < "", _
* * * * * * * * * * budget, .Offset(0, 5).Value = "")
* * * * * * * * *On Error GoTo 0
* * * * * * End With
* * * * End If
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub- Hide quoted text -

- Show quoted text -

Thanks for replying John. but i need it in the sheet as i need to
copy the worksheet into other places and if i copy it i need to copy
the macro as well but if i have a combine code in sheet then i dont
need to copy the macro as the worksheet macro get copy it self when
you copy the sheet to some other place. can you please help me that
how can i combine this code

You can put them all on the same sheet:
Sub Worksheet_Change(ByVal Target As Range)
Macro1()
Macro2()
End Sub

Private SubMacro1()
Const WS_RANGE As String = "B25:D62,K25:K62"


On Error GoTo ws_exit
Application.EnableEvents = False


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If


End If
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub





Private Sub Macro2()
Dim MyRange As Range
Set MyRange = Range("F25:F62")
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
budget = budget + c.Value
End If
End If
Next c
If Target.Value < "" Then
Target.Offset(0, 5).Value = budget
Else
Target.Offset(0, 5).Value = ""
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"K" wrote:

On Dec 31, 4:11 pm, John Bundy (remove) wrote:
The easiest way is to just give them names, make one say sub Macro1() and the
other sub macro2(), better names would help:) then just call them from the
worksheet change, so
Private Sub Worksheet_Change(ByVal Target As Range)
macro1
macro2
End sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.



"K" wrote:
Hi, i have two macro codes and they both are
Private Sub Worksheet_Change(ByVal Target As Range). I want to mix
them and want to make one code. Please see the Macros belw

Macro 1:-

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B25:D62,K25:K62"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If

End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Macro 2:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("F25:F62")
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
budget = budget + c.Value
End If
End If
Next c
If Target.Value < "" Then
Target.Offset(0, 5).Value = budget
Else
Target.Offset(0, 5).Value = ""
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Please can anybody able to tell me that how can i make one macro of
two macros as i cant have two macro in one sheet which starts from
Private Sub Worksheet_Change(ByVal Target As Range)
some one send me the sultion (please see below) but its not working

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "B25:D62,K25:K62"
Const WS_RANGE2 As String = "F25:F62"

On Error GoTo ws_exit
Application.EnableEvents = False

If Target.Cells.Count 1 Then Exit Sub

If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match( _
Me.Cells(.Row, "O").Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
End If

End If
End With
ElseIf Not Intersect(Target, Range(WS_RANGE2)) Is Nothing Then
If IsNumeric(Target) Then
With Target
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup( _
.Offset(0, 8).Value, Range("AB1:AC9995"), 2,
False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = .Offset(0, 9).Value
Then
budget = budget + c.Value
End If
End If
Next c
.Offset(0, 5).Value = IIf(.Value < "", _
budget, .Offset(0, 5).Value = "")
On Error GoTo 0
End With
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub- Hide quoted text -

- Show quoted text -

Thanks for replying John. but i need it in the sheet as i need to
copy the worksheet into other places and if i copy it i need to copy
the macro as well but if i have a combine code in sheet then i dont
need to copy the macro as the worksheet macro get copy it self when
you copy the sheet to some other place. can you please help me that
how can i combine this code

On Dec 31 2007, 5:53*pm, John Bundy (remove) wrote:
You can put them all on the same sheet:
Sub Worksheet_Change(ByVal Target As Range)
Macro1()
Macro2()
End Sub

Private SubMacro1()
Const WS_RANGE As String = "B25:D62,K25:K62"

* * On Error GoTo ws_exit
* * Application.EnableEvents = False

* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * * With Target
* * * * * * If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * * Me.Cells(.Row, "C").Value < "" Then
* * * * * * * * If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
* * * * * * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * * * End If
* * * * * * * * If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * * MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * End If

* * * * * * End If
* * * * End With
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub

Private Sub Macro2()
Dim MyRange As Range
Set MyRange = Range("F25:F62")
* *If Target.Cells.Count 1 Then Exit Sub
* * If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
* * * * If IsNumeric(Target) Then
* * * * * * On Error Resume Next
* * * * * * Application.EnableEvents = False
* * * * * * budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
* * If c.Address < Target.Address Then
* * * * If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
* * * * * *budget = budget + c.Value
* * * * End If
* * End If
Next c
* * * * * *If Target.Value < "" Then
* * * * * * * * Target.Offset(0, 5).Value = budget
* * * * * * Else
* * * * * *Target.Offset(0, 5).Value = ""
* * * * * *End If
* * * * * * * * Application.EnableEvents = True
* * * * * * On Error GoTo 0
* * * * End If
* * End If
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.



"K" wrote:
On Dec 31, 4:11 pm, John Bundy (remove) wrote:
The easiest way is to just give them names, make one say sub Macro1() and the
other sub macro2(), better names would help:) then just call them from the
worksheet change, so
Private Sub Worksheet_Change(ByVal Target As Range)
macro1
macro2
End sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"K" wrote:
Hi, i have two macro codes and they both are
Private Sub Worksheet_Change(ByVal Target As Range). *I want to mix
them and want to make one code. *Please see the Macros belw

Macro 1:-

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B25:D62,K25:K62"

* * On Error GoTo ws_exit
* * Application.EnableEvents = False

* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * * With Target
* * * * * * If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * * Me.Cells(.Row, "C").Value < "" Then
* * * * * * * * If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
* * * * * * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * * * End If
* * * * * * * * If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * * MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * End If

* * * * * * End If
* * * * End With
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub

Macro 2:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("F25:F62")
* *If Target.Cells.Count 1 Then Exit Sub
* * If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
* * * * If IsNumeric(Target) Then
* * * * * * On Error Resume Next
* * * * * * Application.EnableEvents = False
* * * * * * budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
* * If c.Address < Target.Address Then
* * * * If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
* * * * * *budget = budget + c.Value
* * * * End If
* * End If
Next c
* * * * * *If Target.Value < "" Then
* * * * * * * * Target.Offset(0, 5).Value = budget
* * * * * * Else
* * * * * *Target.Offset(0, 5).Value = ""
* * * * * *End If
* * * * * * * * Application.EnableEvents = True
* * * * * * On Error GoTo 0
* * * * End If
* * End If
End Sub

Please can anybody able to tell me that how can i make one macro of
two macros as i cant have two macro in one sheet which starts from
Private Sub Worksheet_Change(ByVal Target As Range)
some one send me the sultion (please see below) but its not working

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "B25:D62,K25:K62"
Const WS_RANGE2 As String = "F25:F62"

* * On Error GoTo ws_exit
* * Application.EnableEvents = False

* * If Target.Cells.Count 1 Then Exit Sub

* * If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
* * * * With Target
* * * * * * If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * * Me.Cells(.Row, "C").Value < "" Then
* * * * * * * * If IsError(Application.Match( _
* * * * * * * * * * Me.Cells(.Row, "O").Value, Columns(27), 0)) Then
* * * * * * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
* * * * * * * * End If
* * * * * * * * If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * * MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
* * * * * * End If

* * * * * * End If
* * * * End With
* * ElseIf Not Intersect(Target, Range(WS_RANGE2)) Is Nothing Then
* * * * If IsNumeric(Target) Then
* * * * * * With Target
* * * * * * * * *On Error Resume Next
* * * * * * * * *Application.EnableEvents = False
* * * * * * * * *budget = WorksheetFunction.VLookup( _
* * * * * * * * * * .Offset(0, 8).Value, Range("AB1:AC9995"), 2,
False)
* * * * * * * * *For Each c In MyRange
* * * * * * * * * * *If c.Address < Target.Address Then
* * * * * * * * * * * * *If c.Offset(0, 9)..Value = .Offset(0, 9).Value
Then
* * * * * * * * * * * * * * budget = budget + c.Value
* * * * * * * * * * * * *End If
* * * * * * * * * * *End If
* * * * * * * * *Next c
* * * * * * * * *.Offset(0, 5).Value = IIf(.Value < "", _
* * * * * * * * * * budget, .Offset(0, 5).Value = "")
* * * * * * * * *On Error GoTo 0
* * * * * * End With
* * * * End If
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub- Hide quoted text -

- Show quoted text -

Thanks for replying John. *but i need it in the sheet as i need to
copy the worksheet into other places and if i copy it i need to copy
the macro as well but if i have a combine code in sheet then i dont
need to copy the macro as the worksheet macro get copy it self when
you copy the sheet to some other place. *can you please help me that
how can i combine this code- Hide quoted text -

- Show quoted text -

Thanks for replying John and Rick. I'll let you know after appling
this code

On 31 Dec 2007, 17:53, John Bundy (remove) wrote:
You can put them all on the same sheet:
Sub Worksheet_Change(ByVal Target As Range)
Macro1()
Macro2()
End Sub

Private SubMacro1()
Const WS_RANGE As String = "B25:D62,K25:K62"

* * On Error GoTo ws_exit
* * Application.EnableEvents = False

* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * * With Target
* * * * * * If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * * Me.Cells(.Row, "C").Value < "" Then
* * * * * * * * If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
* * * * * * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * * * End If
* * * * * * * * If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * * MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * End If

* * * * * * End If
* * * * End With
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub

Private Sub Macro2()
Dim MyRange As Range
Set MyRange = Range("F25:F62")
* *If Target.Cells.Count 1 Then Exit Sub
* * If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
* * * * If IsNumeric(Target) Then
* * * * * * On Error Resume Next
* * * * * * Application.EnableEvents = False
* * * * * * budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
* * If c.Address < Target.Address Then
* * * * If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
* * * * * *budget = budget + c.Value
* * * * End If
* * End If
Next c
* * * * * *If Target.Value < "" Then
* * * * * * * * Target.Offset(0, 5).Value = budget
* * * * * * Else
* * * * * *Target.Offset(0, 5).Value = ""
* * * * * *End If
* * * * * * * * Application.EnableEvents = True
* * * * * * On Error GoTo 0
* * * * End If
* * End If
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.



"K" wrote:
On Dec 31, 4:11 pm, John Bundy (remove) wrote:
The easiest way is to just give them names, make one say sub Macro1() and the
other sub macro2(), better names would help:) then just call them from the
worksheet change, so
Private Sub Worksheet_Change(ByVal Target As Range)
macro1
macro2
End sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"K" wrote:
Hi, i have two macro codes and they both are
Private Sub Worksheet_Change(ByVal Target As Range). *I want to mix
them and want to make one code. *Please see the Macros belw

Macro 1:-

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B25:D62,K25:K62"

* * On Error GoTo ws_exit
* * Application.EnableEvents = False

* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * * With Target
* * * * * * If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * * Me.Cells(.Row, "C").Value < "" Then
* * * * * * * * If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
* * * * * * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * * * End If
* * * * * * * * If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * * MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * End If

* * * * * * End If
* * * * End With
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub

Macro 2:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("F25:F62")
* *If Target.Cells.Count 1 Then Exit Sub
* * If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
* * * * If IsNumeric(Target) Then
* * * * * * On Error Resume Next
* * * * * * Application.EnableEvents = False
* * * * * * budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
* * If c.Address < Target.Address Then
* * * * If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
* * * * * *budget = budget + c.Value
* * * * End If
* * End If
Next c
* * * * * *If Target.Value < "" Then
* * * * * * * * Target.Offset(0, 5).Value = budget
* * * * * * Else
* * * * * *Target.Offset(0, 5).Value = ""
* * * * * *End If
* * * * * * * * Application.EnableEvents = True
* * * * * * On Error GoTo 0
* * * * End If
* * End If
End Sub

Please can anybody able to tell me that how can i make one macro of
two macros as i cant have two macro in one sheet which starts from
Private Sub Worksheet_Change(ByVal Target As Range)
some one send me the sultion (please see below) but its not working

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "B25:D62,K25:K62"
Const WS_RANGE2 As String = "F25:F62"

* * On Error GoTo ws_exit
* * Application.EnableEvents = False

* * If Target.Cells.Count 1 Then Exit Sub

* * If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
* * * * With Target
* * * * * * If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * * Me.Cells(.Row, "C").Value < "" Then
* * * * * * * * If IsError(Application.Match( _
* * * * * * * * * * Me.Cells(.Row, "O").Value, Columns(27), 0)) Then
* * * * * * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
* * * * * * * * End If
* * * * * * * * If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * * MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
* * * * * * End If

* * * * * * End If
* * * * End With
* * ElseIf Not Intersect(Target, Range(WS_RANGE2)) Is Nothing Then
* * * * If IsNumeric(Target) Then
* * * * * * With Target
* * * * * * * * *On Error Resume Next
* * * * * * * * *Application.EnableEvents = False
* * * * * * * * *budget = WorksheetFunction.VLookup( _
* * * * * * * * * * .Offset(0, 8).Value, Range("AB1:AC9995"), 2,
False)
* * * * * * * * *For Each c In MyRange
* * * * * * * * * * *If c.Address < Target.Address Then
* * * * * * * * * * * * *If c.Offset(0, 9)..Value = .Offset(0, 9).Value
Then
* * * * * * * * * * * * * * budget = budget + c.Value
* * * * * * * * * * * * *End If
* * * * * * * * * * *End If
* * * * * * * * *Next c
* * * * * * * * *.Offset(0, 5).Value = IIf(.Value < "", _
* * * * * * * * * * budget, .Offset(0, 5).Value = "")
* * * * * * * * *On Error GoTo 0
* * * * * * End With
* * * * End If
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub- Hide quoted text -

- Show quoted text -

Thanks for replying John. *but i need it in the sheet as i need to
copy the worksheet into other places and if i copy it i need to copy
the macro as well but if i have a combine code in sheet then i dont
need to copy the macro as the worksheet macro get copy it self when
you copy the sheet to some other place. *can you please help me that
how can i combine this code- Hide quoted text -

- Show quoted text -

Thanks John Bundy remove and Rick for replying. sorry John i tried
the way you told me but it didnt work but the way Rick told me it did
work but i have just small problem that in his code where it say
"If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"Information"
only this function is not working but rest of it is working fine.
normall by formula i get text in row "K" of "ZERO BUDGET" and i want a
message that there is Zero Budget etc. The way Rick told me is
working fine its much better than the macro i had last time from one
of online friend but only this small thing which i mentioned above is
not working. if you have answer please let me know.

Thanks John Bundy remove and Rick for replying. sorry
John i tried the way you told me but it didnt work but the
way Rick told me it did work but i have just small problem
that in his code where it say
"If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"Information"
only this function is not working but rest of it is working fine.
normall by formula i get text in row "K" of "ZERO BUDGET"
and i want a message that there is Zero Budget etc. The way
Rick told me is working fine its much better than the macro i
had last time from one of online friend but only this small thing
which i mentioned above is not working. if you have answer
please let me know.

You probably should have posted your question directly to me under the
sub-thread where I posted my code. Anyway, you need to understand I did not
test out the code I posted because I do not have a way to set things up here
to match your data. What I did is take your two event procedures and simply
"lumped" them together. What I did is assume they worked fine separately and
tried to do my best to combine them... I could easily have missed something.
You say the statement you posted is not working... could you explain in what
way it is not working so I might have an idea where to look for a solution?
Perhaps if you explained the situation, what you want to have happen and
what is actually happening, that might help.

Rick

Are both macro codes supposed to run every time, together at the same time?
Or is there some (unstated) condition which governs when one's code runs and
the other's does not? In other words, we need a little more information
about when the code from each macro is supposed to be executed. What is the
governing condition... perhaps the number of selected cells (one as opposed
to several)?

Rick


"K" wrote in message
...
Hi, i have two macro codes and they both are
Private Sub Worksheet_Change(ByVal Target As Range). I want to mix
them and want to make one code. Please see the Macros belw

Macro 1:-


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B25:D62,K25:K62"


On Error GoTo ws_exit
Application.EnableEvents = False


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
End If


End If
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


Macro 2:-


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("F25:F62")
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
budget = budget + c.Value
End If
End If
Next c
If Target.Value < "" Then
Target.Offset(0, 5).Value = budget
Else
Target.Offset(0, 5).Value = ""
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Please can anybody able to tell me that how can i make one macro of
two macros as i cant have two macro in one sheet which starts from
Private Sub Worksheet_Change(ByVal Target As Range)
some one send me the sultion (please see below) but its not working




Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "B25:D62,K25:K62"
Const WS_RANGE2 As String = "F25:F62"


On Error GoTo ws_exit
Application.EnableEvents = False


If Target.Cells.Count 1 Then Exit Sub


If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match( _
Me.Cells(.Row, "O").Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
End If


End If
End With
ElseIf Not Intersect(Target, Range(WS_RANGE2)) Is Nothing Then
If IsNumeric(Target) Then
With Target
On Error Resume Next
Application.EnableEvents = False
budget = WorksheetFunction.VLookup( _
.Offset(0, 8).Value, Range("AB1:AC9995"), 2,
False)
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = .Offset(0, 9).Value
Then
budget = budget + c.Value
End If
End If
Next c
.Offset(0, 5).Value = IIf(.Value < "", _
budget, .Offset(0, 5).Value = "")
On Error GoTo 0
End With
End If
End If


ws_exit:
Application.EnableEvents = True
End Sub

On Dec 31, 4:46*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Are both macro codes supposed to run every time, together at the same time?
Or is there some (unstated) condition which governs when one's code runs and
the other's does not? In other words, we need a little more information
about when the code from each macro is supposed to be executed. What is the
governing condition... perhaps the number of selected cells (one as opposed
to several)?

Rick

"K" wrote in message

...



Hi, i have two macro codes and they both are
Private Sub Worksheet_Change(ByVal Target As Range). *I want to mix
them and want to make one code. *Please see the Macros belw

Macro 1:-

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B25:D62,K25:K62"

* *On Error GoTo ws_exit
* *Application.EnableEvents = False

* *If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * *With Target
* * * * * *If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * *Me.Cells(.Row, "C").Value < "" Then
* * * * * * * *If IsError(Application.Match(Me.Cells(.Row,
O).Value, Columns(27), 0)) Then
* * * * * * * * * *MsgBox "NO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * * * *End If
* * * * * * * *If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * *MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
INFORMATION
* * * * * *End If

* * * * * *End If
* * * *End With
* *End If

ws_exit:
* *Application.EnableEvents = True
End Sub

Macro 2:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("F25:F62")
* If Target.Cells.Count 1 Then Exit Sub
* *If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
* * * *If IsNumeric(Target) Then
* * * * * *On Error Resume Next
* * * * * *Application.EnableEvents = False
* * * * * *budget = WorksheetFunction.VLookup(Target.Offset(0,
9).Value, Range("AB1:AC9995"), 2, False)
For Each c In MyRange
* *If c.Address < Target.Address Then
* * * *If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
* * * * * budget = budget + c.Value
* * * *End If
* *End If
Next c
* * * * * If Target.Value < "" Then
* * * * * * * *Target.Offset(0, 5).Value = budget
* * * * * *Else
* * * * * Target.Offset(0, 5).Value = ""
* * * * * End If
* * * * * * * *Application.EnableEvents = True
* * * * * *On Error GoTo 0
* * * *End If
* *End If
End Sub

Please can anybody able to tell me that how can i make one macro of
two macros as i cant have two macro in one sheet which starts from
Private Sub Worksheet_Change(ByVal Target As Range)
some one send me the sultion (please see below) but its not working

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE1 As String = "B25:D62,K25:K62"
Const WS_RANGE2 As String = "F25:F62"

* *On Error GoTo ws_exit
* *Application.EnableEvents = False

* *If Target.Cells.Count 1 Then Exit Sub

* *If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
* * * *With Target
* * * * * *If Me.Cells(.Row, "B").Value < "" And _
* * * * * * * *Me.Cells(.Row, "C").Value < "" Then
* * * * * * * *If IsError(Application.Match( _
* * * * * * * * * *Me.Cells(.Row, "O").Value, Columns(27), 0)) Then
* * * * * * * * * *MsgBox "NO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
* * * * * * * *End If
* * * * * * * *If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
* * * * * * * *MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,
"INFORMATION"
* * * * * *End If

* * * * * *End If
* * * *End With
* *ElseIf Not Intersect(Target, Range(WS_RANGE2)) Is Nothing Then
* * * *If IsNumeric(Target) Then
* * * * * *With Target
* * * * * * * * On Error Resume Next
* * * * * * * * Application.EnableEvents = False
* * * * * * * * budget = WorksheetFunction.VLookup( _
* * * * * * * * * *.Offset(0, 8).Value, Range("AB1:AC9995"), 2,
False)
* * * * * * * * For Each c In MyRange
* * * * * * * * * * If c.Address < Target.Address Then
* * * * * * * * * * * * If c.Offset(0, 9).Value = .Offset(0, 9).Value
Then
* * * * * * * * * * * * * *budget = budget + c.Value
* * * * * * * * * * * * End If
* * * * * * * * * * End If
* * * * * * * * Next c
* * * * * * * * .Offset(0, 5).Value = IIf(.Value < "", _
* * * * * * * * * *budget, .Offset(0, 5).Value = "")
* * * * * * * * On Error GoTo 0
* * * * * *End With
* * * *End If
* *End If

ws_exit:
* *Application.EnableEvents = True
End Sub- Hide quoted text -

- Show quoted text -

thanks for replying Rick, yes both codes should run as one code give
messages if budget is not available and other one tells that how much
budget i have. both macro work perfect speratly but i need to combin
them as you know cant have two "Private Sub Worksheet_Change(ByVal
Target As Range)" in one sheet. I tried every way but the best way
which i think if these code get combine. and i dont need them as
other module macros these code need to be in Sheet macro.

Are both macro codes supposed to run every time, together at
the same time? Or is there some (unstated) condition which
governs when one's code runs and the other's does not? In
other words, we need a little more information about when the
code from each macro is supposed to be executed. What is the
governing condition... perhaps the number of selected cells (one
as opposed to several)?

thanks for replying Rick, yes both codes should run as one code
give messages if budget is not available and other one tells that
how much budget i have. both macro work perfect speratly but I
need to combine them as you know cant have two
"Private Sub Worksheet_Change(ByVal Target As Range)" in
one sheet. I tried every way but the best way which i think if
these code get combine. and i dont need them as other module
macros these code need to be in Sheet macro.

You will have to test it out, of course, but I think the code below
correctly joins your two Worksheet Change event procedures into a single
Change event procedure.

Rick

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Const WS_RANGE As String = "B25:D62,K25:K62"
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Me.Cells(.Row, "B").Value < "" And _
Me.Cells(.Row, "C").Value < "" Then
If IsError(Application.Match( _
Me.Cells(.Row, O).Value, Columns(27), 0)) Then
MsgBox "NO BUDGET IN AGRESSO", vbInformation, Information
End If
If Me.Cells(.Row, "K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation, Information
End If
End If
End With
End If
Set MyRange = Range("F25:F62")
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("F25:F62")) Is Nothing Then
If IsNumeric(Target) Then
budget = WorksheetFunction.VLookup(Target.Offset(0, 9).Value, _
Range("AB1:AC9995"), 2, False)
On Error Resume Next
For Each c In MyRange
If c.Address < Target.Address Then
If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then
budget = budget + c.Value
End If
End If
Next c
On Error GoTo ws_exit
If Target.Value < "" Then
Target.Offset(0, 5).Value = budget
Else
Target.Offset(0, 5).Value = ""
End If
End If
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub