Call 2nd Macro

Please post any questions regarding the program here.

Moderator: 2020vision

Call 2nd Macro

Postby SWV » Wed Feb 16, 2022 10:07 pm

Hi. Looking for a little help with the following code please.

What I am trying to achieve is the following;
If the following four conditions are met then Macro 1 is called.
Q1 = 1
E2 = In Play
F2 = Suspended
R2 = R1

Then if the next condition is met Macro 2 is called.
F2 = Closed

The first part seems to work ok, Macro 1 is called but I cannot get the second part to work in calling Macro 2.

Any ideas why please?
TIA


Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Columns.Count = 16 Then
    Application.EnableEvents = False
     
     If Range("Q1").Value = 1 Then
     If Range("E2").Value = "In Play" Then
      If Range("F2").Value = "Suspended" Then
        If Range("R2").Value = Range("R1").Value Then
            Call Call Macro1

            ElseIf Range("F2").Value = "Closed" Then
                        Call Macro2
       
           End If
        End If
   End If
   End If
   End If
       
     Application.EnableEvents = True
End Sub
SWV
 
Posts: 61
Joined: Sat Aug 12, 2017 8:11 am

Re: Call 2nd Macro

Postby Captain Sensible » Thu Feb 17, 2022 11:43 am

You need to sort out the placement of your end if's, at the moment the code will only get to the elseif if the first condition is met so F2 can't be both Suspended and Closed,

Code: Select all
 If Range("F2").Value = "Suspended" Then
         

                                       If Range("R2").Value = Range("R1").Value Then
                                                  Call Call Macro1

                                         ElseIf Range("F2").Value = "Closed" Then
                                                     Call Macro2
       
                                          End If


        End If


Just needs rearranging to

Code: Select all
 If Range("F2").Value = "Suspended" Then
         

                                       If Range("R2").Value = Range("R1").Value Then
                                                  Call Call Macro1
                                        End If

               ElseIf Range("F2").Value = "Closed" Then
                                                     Call Macro2
       
                                         


               End If
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Call 2nd Macro

Postby SWV » Thu Feb 17, 2022 8:57 pm

I get a compile error with this.

Thanks but that's it for me. vba is just ridiculous. I'm out. I cannot do this.

Goodbye
SWV
 
Posts: 61
Joined: Sat Aug 12, 2017 8:11 am

Re: Call 2nd Macro

Postby Captain Sensible » Thu Feb 17, 2022 11:20 pm

No problem, the error might be because there's a line

Code: Select all
Call Call Macro1


I just copied and pasted your code but should be
Code: Select all
Call Macro1


So the code would be something like

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Columns.Count = 16 Then  'this is condition 1
    Application.EnableEvents = False
     
     If Range("Q1").Value = 1 Then 'this is condition 2
       
        If Range("E2").Value = "In Play" Then ' this is condition 3
           
            If Range("F2").Value = "Suspended" Then ' this is  condition 4
       
                If Range("R2").Value = Range("R1").Value Then '  this is condition 5
                    Call Macro1
                End If ' this ends condition 5

            ElseIf Range("F2").Value = "Closed" Then ' this elseif is within condition 4
                    Call Macro2
       
           
            End If ' this  ends condition 4
       
       
        End If ' this ends condition 3
   
    End If ' this ends condition 2
   
   End If ' this ends condition 1
       
     Application.EnableEvents = True
End Sub


With your code you have lots of loops within loops many of which can be simplified just adding conditions together with AND

What I am trying to achieve is the following;
If the following four conditions are met then Macro 1 is called.
Q1 = 1
E2 = In Play
F2 = Suspended
R2 = R1


For above you could write it in one line rather than having things in loops


Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Columns.Count = 16 Then  'this is condition 1
    Application.EnableEvents = False
     
If Range("Q1").Value = 1 And Range("E2").Value = "In Play" And Range("R2").Value = Range("R1").Value Then
    If Range("F2").Value = "Suspended" Then
        Call Macro1
    ElseIf Range("F2").Value = "Closed" Then
        Call Macro2
    End If
   
End If ' this ends condition 1
       
     Application.EnableEvents = True
End Sub


That's assuming Macro2 is also reliant on Q1 = 1,E2 = In Play and R2 = R1.

VBA can be a pain but once you understand the basics it'll fall into place very quickly.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Call 2nd Macro

Postby SWV » Sun Feb 20, 2022 7:05 pm

Thanks once again Captain 8)

I’ve just seen this thread and can’t believe I only posted my rant on Thursday as I’ve made huge strides over the weekend. My sheet is now working 8)
Great advice from you again thanks :)
SWV
 
Posts: 61
Joined: Sat Aug 12, 2017 8:11 am

Re: Call 2nd Macro

Postby Captain Sensible » Sun Feb 20, 2022 7:30 pm

No problem, we've all been there starting out. All my VBA knowledge has come from people on here and Google.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm


Return to Help

Who is online

Users browsing this forum: No registered users and 27 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.