ElseIf works when conditions met, but debug halts even though condition not met

[dazhoop]

Hi. I am a beginner practicing with VBA. I am assigning macros to buttons to make a tennis scoreboard.

If my cells I2 and J2 both = "40" then my ElseIf works properly.

However, when it cycles through again the debug halts at the same ElseIf statement even though both conditions are no longer true, and I just can't figure out why.

( I know that it errors out when I2 = "Adv", I haven't gotten to that part yet, but when I2 = "40" and J2 = "Adv" I would expect it to move the next ElseIf after the one it gets stuck on)

Here is my macro. Hope my first attempt at pasting BB code comes through ok.


Hi. I am a beginner practicing with VBA. I am assigning macros to buttons to make a tennis scoreboard.

If my cells I2 and J2 both = "40" then my ElseIf works properly.

However, when it cycles through again the debug halts at the same ElseIf statement even though both conditions are no longer true, and I just can't figure out why.

( I know that it errors out when I2 = "Adv", I haven't gotten to that part yet, but when I2 = "40" and J2 = "Adv" I would expect it to move the next ElseIf after the one it gets stuck on)

Here is my macro. Hope my first attempt at pasting BB code comes through ok.


Sub Player1_InGame()
Dim Current1 As String * 3
Dim Current2 As String * 3
Current1 = Worksheets("Tracker").Range("I2")
Current2 = Worksheets("Tracker").Range("J2")
If Current1 = 0 Then
Worksheets("Tracker").Range("I2") = 15
ElseIf Current1 = 15 Then
Worksheets("Tracker").Range("I2") = 30
ElseIf Current1 = 30 Then
Worksheets("Tracker").Range("I2") = 40
ElseIf Current1 = 40 And Current2 = 40 Then
Worksheets("Tracker").Range("I2") = "'Adv"
ElseIf Current1 = 40 And Current2 = "Adv" Then
Worksheets("Tracker").Range("I2") = "'Adv"
Worksheets("Tracker").Range("J2") = 40
Else
Worksheets("Tracker").Range("I2") = 0
End If
End Sub

 

[Legacy 68668]

Why you dimmed both variables as String ?

You are comparing Numeric value with String value....

try dimention as Long or Integer

 

[dazhoop]

I can't use Long or Integer, as my variables could be the value "Adv" which is non-numeric.

 

[c_m]

untested but try:

Sub Player1_InGame()
    
    Set Current1 = Worksheets("Tracker").Range("I2")
    Set Current2 = Worksheets("Tracker").Range("J2")

    If Current1 = 0 Then
        Worksheets("Tracker").Range("I2") = 15
    Else
If Current1 = 15 Then
        Worksheets("Tracker").Range("I2") = 30
    Else
If Current1 = 30 Then
        Worksheets("Tracker").Range("I2") = 40
    Else
If Current1 = 40 And Current2 = 40 Then
        Worksheets("Tracker").Range("I2") = "Adv"
    Else
If Current1 = 40 And Current2 = "Adv" Then
        Worksheets("Tracker").Range("I2") = "Adv"
        Worksheets("Tracker").Range("J2") = 40
    Else
        Worksheets("Tracker").Range("I2") = 0
    End If
End If
End If
End If
End If

 End Sub

 

[Legacy 68668]

I can't use Long or Integer, as my variables could be the value "Adv" which is non-numeric.

Variant

 

[dazhoop]

c_m - I had already tried that with the same outcome.

Seiya,

Changing String to Variant is the answer and it works. May I ask why that is, for example, my "manual" says to avoid using Variant which is why I opted for String?

Thanks,

Darren

 

[Norie]

Try using Select...Case rather than If...ElseIf...End If.

 

[Legacy 68668]

Darren

Variant is suppose to be used like this case.

Variant type variable, as you know, can hold any type of value.
So if it stores nuemric value, it turns Variant/Double
or if is stores String value, it turns Variant/String

Therefore, it can be compared with both type of data.

By the way, why did you dimentioned your variable in 3 bytes of String type ?
As string * 3

 

[dazhoop]

Seiya - OK, that makes sense with the Variant definition.

I chose String * 3 as I figured that the biggest value I would have in my Dim was there characters (Adv) - I get the feeling my logic is flawed there?

Norie - I haven't got as far as learning about VBA Case yet, but as I use it frequently in Unix scripting, it will no doubt end up being my preferred choice of command.

 

[Legacy 68668]

Darren

It sure minimize the usage of the memory for that variable, however you will need to adjust * 3 part as the variable length need to be expand and memory these days is suffient to hold.

I only use that type of dim statement when I need to ristrict the value in 3 bytes for the output purpose only.