code wont run

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
509
Sub Sheet2_Button185_Click()
Dim A As String
Dim txt As String
Dim hf As Variant
Dim g As Variant
Dim q As Variant
If Range("s12") = 2 Then GoTo one:
*** the above line does not execute ***
g = Range("C" & Rows.Count).End(xlUp): If g = 0 Then Range("z" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("g1").Value: Range("g1") = Range("a1"): Range("a1") = Range("z1")
q = Range("e" & Rows.Count).End(xlUp): If q = 0 Then Range("z" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("a1").Value: Range("a1") = Range("z1")
Range("z1") = Range("z2"): Range("z2") = Range("z3"): Range("z3") = Range("z4"): Range("z4") = Range("z5")
If Sheets("sheet1").Range("e17") = 6 Then Range("z5").ClearContents
If Sheets("sheet1").Range("e17") = 5 Then Range("z4").ClearContents
If Sheets("sheet1").Range("e17") = 4 Then Range("z3").ClearContents
If Sheets("sheet1").Range("e17") = 3 Then Range("z2").ClearContents
GoTo two:
one:
q = Range("e" & Rows.Count).End(xlUp): If q = 0 Then GoTo two:
A = Range("G1"): Range("G1") = Range("A1"): Range("A1") = A
two:
Range("A2:G30").ClearContents
Range("H11") = 1: Range("i11") = 1
Range("B1").Select
txt = Range("A1")
Application.Wait Now + TimeSerial(0, 0, 2)
Application.Speech.Speak " it IS "
Application.Speech.Speak txt
Application.Speech.Speak " TO THROW FIRST GAME ON"
end sub

hi all
i have the above code which works ok except for the highlighted line, the code does not
want to know it. if i check the code using the step into, ie pressing f8 it works fine
can someone please point me in the right direction to solve this problem

cheers
kev
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
I'm not sure what the problem was, but try something like this...

Code:
Sub Sheet2_Button185_Click()
    Dim A As String
    Dim txt As String
    Dim hf As Variant
    Dim g As Variant
    Dim q As Variant
    
    If Range("S12") = 2 Then
        q = Range("e" & Rows.Count).End(xlUp)
        If q = 0 Then
            A = Range("G1")
            Range("G1") = Range("A1")
            Range("A1") = A
        End If
    Else
        g = Range("C" & Rows.Count).End(xlUp): If g = 0 Then Range("z" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("g1").Value: Range("g1") = Range("a1"): Range("a1") = Range("z1")
        q = Range("e" & Rows.Count).End(xlUp): If q = 0 Then Range("z" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("a1").Value: Range("a1") = Range("z1")
        Range("z1") = Range("z2"): Range("z2") = Range("z3"): Range("z3") = Range("z4"): Range("z4") = Range("z5")
        If Sheets("sheet1").Range("e17") = 6 Then Range("z5").ClearContents
        If Sheets("sheet1").Range("e17") = 5 Then Range("z4").ClearContents
        If Sheets("sheet1").Range("e17") = 4 Then Range("z3").ClearContents
        If Sheets("sheet1").Range("e17") = 3 Then Range("z2").ClearContents
    End If

    Range("A2:G30").ClearContents
    Range("H11") = 1: Range("i11") = 1
    Range("B1").Select
    txt = Range("A1")
    Application.Wait Now + TimeSerial(0, 0, 2)
    Application.Speech.Speak " it IS "
    Application.Speech.Speak txt
    Application.Speech.Speak " TO THROW FIRST GAME ON"
    
End Sub
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
.... it's not it ???? sorry i thought i got it .... but not

what do you mean by "DOES NOT EXECUTE" ?
 
Last edited:

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
509
hi alphafrog
i tried your suggestion but it did not change anything
it does not appear to execute the first if statement but it will using f8
range s12 does hold the value 2
any other suggestions ?
cheers mate
kev
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450

ADVERTISEMENT

Is the value in cell S12 a text 2 or a numeric 2? Maybe it has a space in front of the 2 or after the 2? I'm just guessing here.

Not to question the obvious, but I have to ask because I can't see your workbook. Is Button185 located on Sheet2? And Sheet2 is the same sheet with cell S12 with the value 2 ?

Another guess, try changing it to
Range("S12").Value
 

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
509
hi alphafrog
s12 holds a numeric 2 which is entered from the keyboard at present, when code is completed it will be sent there from sheet1.
and yes button 185 and s12 are both on sheet2
thanks for your help
kev
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450

ADVERTISEMENT

Well I'm stumped.

If you put a message box after the IF statment, do you see the message pop up?

Code:
    If Range("S12").Value = 2 Then
        [COLOR="Red"]MsgBox "Cell S12 equals 2"[/COLOR]
        q = Range("e" & Rows.Count).End(xlUp)


Or if you set the value in S12 to 2 before the IF statement, then it HAS to be true
Code:
    [COLOR="Red"]Range("S12").Value = 2[/COLOR]
    If Range("S12").Value = 2 Then
        MsgBox "Cell S12 equals 2"
        q = Range("e" & Rows.Count).End(xlUp)
 

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
509
hi alphafrog
i done as you suggested i actually put both suggestions in the code.
the outcome was the number 2 did not appear in s12 and the message box did not appear.
cheers
kev
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
My next question is if the button you are clicking on is running this code at all? It seems that it is not. That would explain why you can step through the code manually but nothing happens when you click the button.
 

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
509
hi alphafrog
the code is working ok there is some more code before the if statement which works fine,
i removed it from the post to make sure that nothing before the if statement was affecting
the code and everything after the if statement works ok. it just does'nt want to know the if statement
cheers
kev
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,659
Messages
5,832,986
Members
430,180
Latest member
woon8888

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top