FALSE instead of null in cell

J

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
348
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Fluff said:
Depends on the code, can you please post it?
Click to expand...

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
 
Upvote 0
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
 
Upvote 0
Fluff said:
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
Click to expand...

So in Case 1, I should remove the If statement and Case 12, include the if statement?
 
Upvote 0
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
 
Upvote 0
Fluff said:
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
Click to expand...

Instead of the entire Select Case statement?
 
Upvote 0
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???
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
13
Views
201
mumps
mumps
R
  • Question
Case select error on string detection?
Replies
7
Views
270
jkpieterse
jkpieterse
P
  • Question
Blank Cell Instead of "False" in this formula
Replies
1
Views
175
Fluff
Fluff
M
  • Solved
Identical text strings return false when compared.
Replies
5
Views
736
Msgjazz
M
B
  • Solved
Select Case Error Problem
Replies
6
Views
138
bisel
B

Forum statistics

Threads
1,214,614
Messages
6,120,519
Members
448,968
Latest member
Ajax40

Share this page

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