FALSE instead of null in cell

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
267
Hello gang!

Quick question hopefully with a simple answer. Why would I get a FALSE in a cell that I set = to ""? This is in a Select Case statement. TIA
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,935
Office Version
365
Platform
Windows
Depends on the code, can you please post it?
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
267
Depends on the code, can you please post it?
This is just a test module. I don't want to mess with the spreadsheet that I have! I'm just trying to use the Select Case instead of all the other coding. Let me know what you think. TIA

Code:
Sub trial()'    Dim x As Long
'    Dim y As Long
'    Dim z As Long
'    Dim first As Integer
'    Dim second As Integer
'    Dim third As Integer
'    Dim season2 As String
'    Dim season3 As String
     Dim episodes As String
    Dim episodes2 As String
    Dim x As String
    
    x = Sheets("Sheet1").Range("$E6").Value
    season = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    episodes = 12
    episodes2 = 14
    
'    season2 = Sheets("Sheet1").Range("$E3").Value
'    season3 = Sheets("Sheet1").Range("$E3").Value
'
'    first = 10
'    second = 13
'    third = 13
    
    'y = 1
    'x = x + 1
    If IsEmpty(Sheets("Sheet1").Range("$E6")) = True Then
        Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
        If IsEmpty(Sheets("Sheet1").Range("$E3")) = True Then
            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
        End If
    Else
        Select Case x
            Case 1
                    If x = 1 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 2
                If x = 2 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 3
                If x = 3 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 4
                If x = 4 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 5
                If x = 5 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 6
                If x = 6 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 7
                If x = 7 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 8
                If x = 8 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 9
                If x = 9 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 10
                If x = 10 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 11
                If x = 11 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 12
                'If x = 12 Then
                    'Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        'If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = " "
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        'End If
                    'End If
            Case 13
                If x = 13 Then
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        If x = 14 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If
                    End If
            Case 14
                If x = 14 Then
                    'Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        'If x = 14 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        'End If
                    End If
        End Select
                       
   


'            If IsEmpty(Sheets("Sheet1").Range("$E6")) = False Then
'                If Sheets("Sheet1").Range("$E6").Value = third Then
'                Else
'                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
'            End If
'                    If Sheets("Sheet1").Range("$E3").Value = "" Then
'                       Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
'                End If
'                    End If
'                If IsEmpty(Sheets("Sheet1").Range("$E3")) = False Then
'                    If Sheets("Sheet1").Range("$E6").Value = second Then
'                        If season3 = "3" Then
'                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
'                            Sheets("Sheet1").Range("$E6").Value = ""
'                        Else
'                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
'                            Sheets("Sheet1").Range("$E6").Value = ""
'                        End If
'                    End If
'                End If
'                If IsEmpty(Sheets("Sheet1").Range("$E3")) = False Then
'                    If season2 = "2" Then
'                        If season3 = "3" Then
'                    Else
'                    Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
'                    Sheets("Sheet1").Range("$E6").Value = ""
'                End If
'            End If
'        End If
    End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,935
Office Version
365
Platform
Windows
A select case is like multiple If statements, so with section of code
Code:
            Case 1
                    [COLOR=#0000ff]If x = 1 Then[/COLOR]
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        [COLOR=#ff0000]If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If[/COLOR]
                    End If
            Case 2
The line in blue is redundant as it will only run that line if x=1, also the part in red is redundant as x can only be 1.

Then if x=12 we get to here
Code:
            Case 12
               [COLOR=#008000] 'If x = 12 Then
                    'Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        'If x = 12 Then[/COLOR]
                            [COLOR=#ff0000]Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = " "[/COLOR]
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                     [COLOR=#008000]   'End If
                    'End If[/COLOR]
            Case 13
Where the line in red is saying E6=E6=" " which is a logical statement, so if E6=" " it will return TRUE otherwise it will return FALSE.
As that will only run if x=12 & x is the value of E6, then 12 does not equal " " so it will always return FALSE.

HTH
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
267
A select case is like multiple If statements, so with section of code
Code:
            Case 1
                    [COLOR=#0000ff]If x = 1 Then[/COLOR]
                    Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        [COLOR=#ff0000]If x = 12 Then
                            Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = ""
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                        End If[/COLOR]
                    End If
            Case 2
The line in blue is redundant as it will only run that line if x=1, also the part in red is redundant as x can only be 1.

Then if x=12 we get to here
Code:
            Case 12
               [COLOR=#008000] 'If x = 12 Then
                    'Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value + 1
                        'If x = 12 Then[/COLOR]
                            [COLOR=#ff0000]Sheets("Sheet1").Range("$E6").Value = Sheets("Sheet1").Range("$E6").Value = " "[/COLOR]
                            Sheets("Sheet1").Range("$E3").Value = Sheets("Sheet1").Range("$E3").Value + 1
                     [COLOR=#008000]   'End If
                    'End If[/COLOR]
            Case 13
Where the line in red is saying E6=E6=" " which is a logical statement, so if E6=" " it will return TRUE otherwise it will return FALSE.
As that will only run if x=12 & x is the value of E6, then 12 does not equal " " so it will always return FALSE.

HTH
So in Case 1, I should remove the If statement and Case 12, include the if statement?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,935
Office Version
365
Platform
Windows
If I understand correctly, you can just do
Code:
Sub trial()
    With Sheets("Sheet1")
        If .Range("E6") = 12 Then
            .Range("E6").Value = ""
            .Range("E3").Value = .Range("E3").Value + 1
        Else
            .Range("E6").Value = .Range("E6").Value + 1
            If IsEmpty(.Range("$E3")) = True Then
                .Range("$E3").Value = .Range("$E3").Value + 1
            End If
        End If
    End With
End Sub
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
267
If I understand correctly, you can just do
Code:
Sub trial()
    With Sheets("Sheet1")
        If .Range("E6") = 12 Then
            .Range("E6").Value = ""
            .Range("E3").Value = .Range("E3").Value + 1
        Else
            .Range("E6").Value = .Range("E6").Value + 1
            If IsEmpty(.Range("$E3")) = True Then
                .Range("$E3").Value = .Range("$E3").Value + 1
            End If
        End If
    End With
End Sub
Instead of the entire Select Case statement?
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
267
I guess I should give a little detail about what it does. E3 is the season of the show while E6 is the episode. 8 of the 10 seasons in this case are 12 episodes, the 9th has 14. Clear as mud lol???
 

Watch MrExcel Video

Forum statistics

Threads
1,090,080
Messages
5,412,253
Members
403,423
Latest member
fori_gump

This Week's Hot Topics

Top