Solved

How would I code ElseIf statements to work on text instead of numbers (value)?

Posted on 2015-01-12
5
115 Views
Last Modified: 2015-01-12
Hi,

Below is the code that I use for cell visibility but it is only for value
Is there a way to get it to recognize text as well? So if, for example, A1='yes' then rows 2:5 are shown but if A1='' then rows 2:5 are hidden

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngIntersect As Range
Set rngIntersect = Intersect(Target, Range("$I$12:$I$1500"))

If Not rngIntersect Is Nothing Then

    If Range("B23").Value = 0 And Range("B24").Value = 0 And Range("B25").Value = 0 Then
            'federal
            Rows("22:33").EntireRow.Hidden = True
        ElseIf Range("B23").Value > 0 Then
            Rows("22:24").EntireRow.Hidden = False
            Rows("32:33").EntireRow.Hidden = False
            Rows("25:31").EntireRow.Hidden = True
        ElseIf Range("B23").Value = 0 And Range("B24").Value > 0 Then
            Rows("22").EntireRow.Hidden = False
            Rows("23").EntireRow.Hidden = True
            Rows("24").EntireRow.Hidden = False
            Rows("25:31").EntireRow.Hidden = True
            Rows("32:33").EntireRow.Hidden = False
        ElseIf Range("B25").Value > 0 Then
            Rows("22").EntireRow.Hidden = False
            Rows("23:24").EntireRow.Hidden = True
            Rows("25:33").EntireRow.Hidden = False
        Else
            Rows("22:33").EntireRow.Hidden = False
        End If

    If Range("B42").Value = 0 And Range("B43").Value = 0 And Range("B44").Value = 0 Then
            'ma return
            Rows("41:52").EntireRow.Hidden = True
        ElseIf Range("B42").Value > 0 Then
            Rows("41:43").EntireRow.Hidden = False
            Rows("51:52").EntireRow.Hidden = False
            Rows("44:50").EntireRow.Hidden = True
        ElseIf Range("B42").Value = 0 And Range("B43").Value > 0 Then
            Rows("41").EntireRow.Hidden = False
            Rows("42").EntireRow.Hidden = True
            Rows("43").EntireRow.Hidden = False
            Rows("44:50").EntireRow.Hidden = True
            Rows("51:52").EntireRow.Hidden = False
        ElseIf Range("B44").Value > 0 Then
            Rows("41").EntireRow.Hidden = False
            Rows("42:43").EntireRow.Hidden = True
            Rows("44:52").EntireRow.Hidden = False
        Else
            Rows("41:52").EntireRow.Hidden = False
        End If
End If
End Sub

Open in new window

0
Comment
Question by:Boston617
  • 3
  • 2
5 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40545192
If A1="Yes"

If A1=""
0
 

Author Comment

by:Boston617
ID: 40545240
I would not need to add .string or .text to the range?
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40545246
Sorry, it should have been

range("A1").value = "Yes"

range("A1").value = ""
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 40545247
.value applies to both text and numbers
0
 

Author Comment

by:Boston617
ID: 40545252
oh really?! thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now