FALSE instead of null in cell

J

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
367
Office Version
  1. 2021
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

E
  • Solved
Can you add NULL (not Blank "") to an If statement in a formula?
2
Replies
15
Views
893
MARK858
MARK858
W
  • Solved
Using MIN with IF resulting in #VALUE error
2
Replies
15
Views
1K
myall_blues
myall_blues
G
  • Question Question
Disable Cut Copy Paste
Replies
1
Views
718
jkpieterse
jkpieterse
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
812
mumps
mumps
R
  • Solved
VBA - Find Replace on selected cells
Replies
3
Views
373
rsulliva
R

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

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