COMBINE TWO MACROS INTO ONE

K

K

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
 
J

John Bundy

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
 
K

K

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.




















- Show quoted text -
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

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

K

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





















- Show quoted text -
Click to expand...

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.
 
R

Rick Rothstein \(MVP - VB\)

Are both macro codes supposed to run every time, together at
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.
Click to expand...

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
 
J

John Bundy

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
 
K

K

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.






- Show quoted text -
Click to expand...

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

K

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.






- Show quoted text -
Click to expand...

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.
 
R

Rick Rothstein \(MVP - VB\)

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.
Click to expand...

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
 
K

K

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
Click to expand...

Hi Rick, sorry as i think you are right as i should've posted my
question directly to you.
I have taken out the line from macro which was "If Me.Cells(.Row,
"K").Value = "ZERO BUDGET" Then
MsgBox "ZERO BUDGET IN AGRESSO", vbInformation,"Information"
and it working fine but i still need this function. please see below
if you can help me in this


I want to add line in macro below that if I have text of "ZERO
BUDGET"
appear in any cell of Range("K25:K62") then I should get
Message box appear that there is Zero budget.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Const WS_RANGE As String = "B25:D62"
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
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HOW TO MIX TWO MACRO CODES 2
CORRECTION NEEDED 2
CAN ANYONE PLEASE SOLVE MY PROBLEM 1
MACRO IS NOT COMPLETE 2
Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Change_Event troubles, no error & no work. 6
Help with this code 9
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0

Top