code wont run

kevin lazell

Active Member
Joined
Jun 2, 2004
Messages
496
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
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,343
.... it's not it ???? sorry i thought i got it .... but not

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

kevin lazell

Active Member
Joined
Jun 2, 2004
Messages
496
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,271

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

Active Member
Joined
Jun 2, 2004
Messages
496
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,271

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

Active Member
Joined
Jun 2, 2004
Messages
496
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,271
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

Active Member
Joined
Jun 2, 2004
Messages
496
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,747
Messages
5,513,153
Members
408,939
Latest member
jessica116

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top