code wont run

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
513
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
.... it's not it ???? sorry i thought i got it .... but not

what do you mean by "DOES NOT EXECUTE" ?
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top