Solved

code stops working near (but not at) end

Posted on 2014-12-30
17
58 Views
Last Modified: 2014-12-31
Hi,

The code I have was working fine until I noticed that the entries for 'wv return and 'ut return weren't working but the code still continues to work well after it. Am I overlooking something?

Below is the code I'm using, the ones I'm having trouble with are near the very bottom
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("B24").Value = 0 Then
        Rows("24:30").EntireRow.Hidden = True
    Else
        Rows("24:30").EntireRow.Hidden = False
    End If
    
    If Range("B23").Value = 0 Then
        Rows("23").EntireRow.Hidden = True
    Else
        Rows("23").EntireRow.Hidden = False
    End If

    If Range("B41").Value = 0 And Range("B42").Value = 0 Then
            'both ranges are zero, hide rows 30-35
            Rows("40:50").EntireRow.Hidden = True
        ElseIf Range("B42").Value > 0 Then
            'range > 0, display rows 32-35
            Rows("42:50").EntireRow.Hidden = False
            Rows("40").EntireRow.Hidden = False
            'hide row 31
            Rows("41").EntireRow.Hidden = True
        ElseIf Range("B41").Value > 0 Then
            'range > 0, display row 31
            Rows("40:41").EntireRow.Hidden = False
            'hide rows 32-35
            Rows("42:48").EntireRow.Hidden = True
        Else
            'display rows 30-35
            Rows("40:50").EntireRow.Hidden = False
        End If
        
    If Range("B52").Value = 0 And Range("B53").Value = 0 Then
            'ny return
            Rows("51:61").EntireRow.Hidden = True
        ElseIf Range("B52").Value > 0 Then
            Rows("53:59").EntireRow.Hidden = True
            Rows("51:52").EntireRow.Hidden = False
            Rows("60:61").EntireRow.Hidden = False
        ElseIf Range("B53").Value > 0 Then
            Rows("53:61").EntireRow.Hidden = False
            Rows("51").EntireRow.Hidden = False
            Rows("52").EntireRow.Hidden = True
        Else
            Rows("51:61").EntireRow.Hidden = False
        End If
        
    If Range("B63").Value = 0 And Range("B64").Value = 0 Then
            'ca return
            Rows("62:71").EntireRow.Hidden = True
        ElseIf Range("B63").Value > 0 Then
            Rows("64:69").EntireRow.Hidden = True
            Rows("62:63").EntireRow.Hidden = False
            Rows("70:71").EntireRow.Hidden = False
        ElseIf Range("B64").Value > 0 Then
            Rows("64:71").EntireRow.Hidden = False
            Rows("62").EntireRow.Hidden = False
            Rows("63").EntireRow.Hidden = True
        Else
            Rows("62:71").EntireRow.Hidden = False
        End If
        
    If Range("B73").Value = 0 And Range("B74").Value = 0 Then
            'ri return
            Rows("72:81").EntireRow.Hidden = True
        ElseIf Range("B73").Value > 0 Then
            Rows("74:79").EntireRow.Hidden = True
            Rows("72:73").EntireRow.Hidden = False
            Rows("80:81").EntireRow.Hidden = False
        ElseIf Range("B74").Value > 0 Then
            Rows("74:81").EntireRow.Hidden = False
            Rows("72").EntireRow.Hidden = False
            Rows("73").EntireRow.Hidden = True
        Else
            Rows("72:81").EntireRow.Hidden = False
        End If
    
    If Range("B83").Value = 0 And Range("B84").Value = 0 Then
            'nj return
            Rows("82:91").EntireRow.Hidden = True
        ElseIf Range("B83").Value > 0 Then
            Rows("84:89").EntireRow.Hidden = True
            Rows("82:83").EntireRow.Hidden = False
            Rows("90:92").EntireRow.Hidden = False
        ElseIf Range("B84").Value > 0 Then
            Rows("84:91").EntireRow.Hidden = False
            Rows("82").EntireRow.Hidden = False
            Rows("83").EntireRow.Hidden = True
        Else
            Rows("82:91").EntireRow.Hidden = False
        End If
        
    If Range("B93").Value = 0 And Range("B94").Value = 0 Then
            'oh return
            Rows("92:101").EntireRow.Hidden = True
        ElseIf Range("B93").Value > 0 Then
            Rows("94:99").EntireRow.Hidden = True
            Rows("92:93").EntireRow.Hidden = False
            Rows("100:101").EntireRow.Hidden = False
        ElseIf Range("B94").Value > 0 Then
            Rows("94:101").EntireRow.Hidden = False
            Rows("92").EntireRow.Hidden = False
            Rows("93").EntireRow.Hidden = True
        Else
            Rows("92:101").EntireRow.Hidden = False
        End If
    
    If Range("B103").Value = 0 And Range("B104").Value = 0 Then
            'ct return
            Rows("102:111").EntireRow.Hidden = True
        ElseIf Range("B103").Value > 0 Then
            Rows("104:109").EntireRow.Hidden = True
            Rows("102:103").EntireRow.Hidden = False
            Rows("110:111").EntireRow.Hidden = False
        ElseIf Range("B104").Value > 0 Then
            Rows("104:111").EntireRow.Hidden = False
            Rows("102").EntireRow.Hidden = False
            Rows("103").EntireRow.Hidden = True
        Else
            Rows("102:111").EntireRow.Hidden = False
        End If
        
    If Range("B113").Value = 0 And Range("B114").Value = 0 Then
            'nc return
            Rows("112:121").EntireRow.Hidden = True
        ElseIf Range("B113").Value > 0 Then
            Rows("114:119").EntireRow.Hidden = True
            Rows("112:113").EntireRow.Hidden = False
            Rows("120:121").EntireRow.Hidden = False
        ElseIf Range("B114").Value > 0 Then
            Rows("114:121").EntireRow.Hidden = False
            Rows("112").EntireRow.Hidden = False
            Rows("113").EntireRow.Hidden = True
        Else
            Rows("112:121").EntireRow.Hidden = False
        End If
    
    If Range("B123").Value = 0 And Range("B124").Value = 0 Then
            'va return
            Rows("122:131").EntireRow.Hidden = True
        ElseIf Range("B123").Value > 0 Then
            Rows("124:129").EntireRow.Hidden = True
            Rows("122:123").EntireRow.Hidden = False
            Rows("130:131").EntireRow.Hidden = False
        ElseIf Range("B124").Value > 0 Then
            Rows("124:131").EntireRow.Hidden = False
            Rows("122").EntireRow.Hidden = False
            Rows("123").EntireRow.Hidden = True
        Else
            Rows("122:131").EntireRow.Hidden = False
        End If
        
    If Range("B133").Value = 0 And Range("B134").Value = 0 Then
            'pa return
            Rows("132:141").EntireRow.Hidden = True
        ElseIf Range("B133").Value > 0 Then
            Rows("134:139").EntireRow.Hidden = True
            Rows("132:133").EntireRow.Hidden = False
            Rows("140:141").EntireRow.Hidden = False
        ElseIf Range("B134").Value > 0 Then
            Rows("134:141").EntireRow.Hidden = False
            Rows("132").EntireRow.Hidden = False
            Rows("133").EntireRow.Hidden = True
        Else
            Rows("132:141").EntireRow.Hidden = False
        End If
        
    If Range("B143").Value = 0 And Range("B144").Value = 0 Then
            'dc return
            Rows("142:151").EntireRow.Hidden = True
        ElseIf Range("B143").Value > 0 Then
            Rows("144:149").EntireRow.Hidden = True
            Rows("142:143").EntireRow.Hidden = False
            Rows("150:151").EntireRow.Hidden = False
        ElseIf Range("B144").Value > 0 Then
            Rows("144:151").EntireRow.Hidden = False
            Rows("142").EntireRow.Hidden = False
            Rows("143").EntireRow.Hidden = True
        Else
            Rows("142:151").EntireRow.Hidden = False
        End If
        
    If Range("B153").Value = 0 And Range("B154").Value = 0 Then
            'ga return
            Rows("152:161").EntireRow.Hidden = True
        ElseIf Range("B153").Value > 0 Then
            Rows("154:159").EntireRow.Hidden = True
            Rows("152:153").EntireRow.Hidden = False
            Rows("160:161").EntireRow.Hidden = False
        ElseIf Range("B154").Value > 0 Then
            Rows("154:161").EntireRow.Hidden = False
            Rows("152").EntireRow.Hidden = False
            Rows("153").EntireRow.Hidden = True
        Else
            Range("152:161").EntireRow.Hidden = False
        End If
        
    If Range("B163").Value = 0 And Range("B164").Value = 0 Then
            'nh return
            Rows("162:171").EntireRow.Hidden = True
        ElseIf Range("B163").Value > 0 Then
            Rows("164:169").EntireRow.Hidden = True
            Rows("162:163").EntireRow.Hidden = False
            Rows("170:171").EntireRow.Hidden = False
        ElseIf Range("B164").Value > 0 Then
            Rows("164:171").EntireRow.Hidden = False
            Rows("152").EntireRow.Hidden = False
            Rows("163").EntireRow.Hidden = True
        Else
            Rows("162:171").EntireRow.Hidden = False
        End If
        
    If Range("B173").Value = 0 And Range("B174").Value = 0 Then
            'vt return
            Rows("172:181").EntireRow.Hidden = True
        ElseIf Range("B173").Value > 0 Then
            Rows("174:179").EntireRow.Hidden = True
            Rows("172:173").EntireRow.Hidden = False
            Rows("180:181").EntireRow.Hidden = False
        ElseIf Range("B174").Value > 0 Then
            Rows("174:181").EntireRow.Hidden = False
            Rows("172").EntireRow.Hidden = False
            Rows("173").EntireRow.Hidden = True
        Else
            Rows("172:181").EntireRow.Hidden = False
        End If
        
    If Range("B183").Value = 0 And Range("B184").Value = 0 Then
            'md return
            Rows("182:191").EntireRow.Hidden = True
        ElseIf Range("B183").Value > 0 Then
            Rows("184:191").EntireRow.Hidden = True
            Rows("182:183").EntireRow.Hidden = False
            Rows("190:191").EntireRow.Hidden = False
        ElseIf Range("B184").Value > 0 Then
            Rows("184:191").EntireRow.Hidden = False
            Rows("182").EntireRow.Hidden = False
            Rows("183").EntireRow.Hidden = True
        Else
            Rows("182:191").EntireRow.Hidden = False
        End If
        
    If Range("B193").Value = 0 And Range("B194").Value = 0 Then
            'il return
            Rows("192:201").EntireRow.Hidden = True
        ElseIf Range("B193").Value > 0 Then
            Rows("194:199").EntireRow.Hidden = True
            Rows("192:193").EntireRow.Hidden = False
            Rows("200:201").EntireRow.Hidden = False
        ElseIf Range("B194").Value > 0 Then
            Rows("194:201").EntireRow.Hidden = False
            Rows("192").EntireRow.Hidden = False
            Rows("193").EntireRow.Hidden = True
        Else
            Rows("192:201").EntireRow.Hidden = False
        End If
        
    If Range("B203").Value = 0 And Range("B204").Value = 0 Then
            'wv return
            Rows("202:211").EntireRow.Hidden = True
        ElseIf Range("B203").Value > 0 Then
            Rows("204:209").EntireRow.Hidden = True
            Rows("202:203").EntireRow.Hidden = False
            Rows("210:211").EntireRow.Hidden = False
        ElseIf Range("B204").Value > 0 Then
            Rows("204:211").EntireRow.Hidden = False
            Rows("202").EntireRow.Hidden = False
            Rows("203").EntireRow.Hidden = True
        Else
            Rows("202:211").EntireRow.Hidden = False
        End If
        
    If Range("B213").Value = 0 And Range("B214").Value = 0 Then
            'ut return
            Rows("212:221").EntireRow.Hidden = True
        ElseIf Range("B213").Value > 0 Then
            Rows("214:219").EntireRow.Hidden = True
            Rows("212:213").EntireRow.Hidden = False
            Rows("200:221").EntireRow.Hidden = False
        ElseIf Range("B214").Value > 0 Then
            Rows("214:221").EntireRow.Hidden = False
            Rows("212").EntireRow.Hidden = False
            Rows("213").EntireRow.Hidden = True
        Else
            Rows("212:221").EntireRow.Hidden = False
        End If
        
    If Range("B223").Value = 0 And Range("B224").Value = 0 Then
            'mi return
            Rows("222:231").EntireRow.Hidden = True
        ElseIf Range("B223").Value > 0 Then
            Rows("224:229").EntireRow.Hidden = True
            Rows("222:223").EntireRow.Hidden = False
            Rows("230:231").EntireRow.Hidden = False
        ElseIf Range("B224").Value > 0 Then
            Rows("224:231").EntireRow.Hidden = False
            Rows("222").EntireRow.Hidden = False
            Rows("223").EntireRow.Hidden = True
        Else
            Rows("222:231").EntireRow.Hidden = False
        End If
        
    If Range("B233").Value = 0 And Range("B234").Value = 0 Then
            'me return
            Rows("232:241").EntireRow.Hidden = True
        ElseIf Range("B233").Value > 0 Then
            Rows("234:239").EntireRow.Hidden = True
            Rows("232:233").EntireRow.Hidden = False
            Rows("240:241").EntireRow.Hidden = False
        ElseIf Range("B234").Value > 0 Then
            Rows("234:241").EntireRow.Hidden = False
            Rows("232").EntireRow.Hidden = False
            Rows("233").EntireRow.Hidden = True
        Else
            Rows("232:241").EntireRow.Hidden = False
        End If
    
End If

End Sub

Open in new window

0
Comment
Question by:Boston617
  • 10
  • 7
17 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40524463
Put a breakpoint on line 263 (click in the left-had margin) and run the macro. What are the values of B203 and B204 at that point.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40524508
Better yet would be to attach your workbook if it doesn't contain sensitive information.
0
 

Author Comment

by:Boston617
ID: 40524529
Hi Martin,

How would I attach my workbook?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 40524536
When you create a post you'll see this. Click it, then click the 'Choose File' button, followed by the green 'Upload File' button.click it
0
 

Author Comment

by:Boston617
ID: 40524546
Below is my workbook, I put a $1 value on a few states to show how it should appear
0
 

Author Comment

by:Boston617
ID: 40524550
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40524574
Given the sheet with the data you posted, what is it doing wrong?
0
 

Author Comment

by:Boston617
ID: 40524582
the entries for 'wv return and 'ut return don't function at all
the entries for the other states all work fine

basically, if there is a refund due, then only the refund amount line will show
if there is an amount due, then lines "Amount Due" down to "Mail Check..." will show
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40524596
For the "wv" processing If I unhide all the rows between 50 and 211 and then select a cell in column "I", the code finds that Range("B203").Value = 1 And Range("B204").Value = 0 so the If Range("B203").Value = 0 And Range("B204").Value = 0 Then is false but the ElseIf Range("B203").Value > 0 is true so Rows("204:209") are hidden and Rows("202:203" and Rows("210:211") are unhidden. What would you like to do differently?
0
 

Author Comment

by:Boston617
ID: 40524613
That is exactly what I want to happen but Rows("204:209") are not hidden, they still show up when they should not

The code doesn't work even if both B203 and B204 = 0, the rows don't collapse as they should
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40524630
It does hide them. Do what I did and first manually unhide a range of rows that include those rows and then run the code. Here's what I see when I do that.
They're hidden
0
 

Author Comment

by:Boston617
ID: 40524635
I can't seem to be able to get it to do that with me

I also really want to do anything manual (clicking on rows, not inputing data) when I work on this
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40524646
When you say "I also really want to do anything manual (clicking on rows, not inputing data) when I work on this" did you mean "I also don't really want to do anything manual (clicking on rows, not inputing data) when I work on this". If so I only unhid the rows so I could see the result. If that's not what you mean then please try to explain again.
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40524663
Oh wait! I see the problem. The code for ut has a typo which I've bolded. I believe it should be 210 and not 200.

    If Range("B213").Value = 0 And Range("B214").Value = 0 Then
            'ut return
            Rows("212:221").EntireRow.Hidden = True
        ElseIf Range("B213").Value > 0 Then
            Rows("214:219").EntireRow.Hidden = True
            Rows("212:213").EntireRow.Hidden = False
            Rows("200:221").EntireRow.Hidden = False
        ElseIf Range("B214").Value > 0 Then
            Rows("214:221").EntireRow.Hidden = False
            Rows("212").EntireRow.Hidden = False
            Rows("213").EntireRow.Hidden = True
        Else
            Rows("212:221").EntireRow.Hidden = False
        End If
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40524669
It looks like you have a second typo.

    If Range("B163").Value = 0 And Range("B164").Value = 0 Then
            'nh return
            Rows("162:171").EntireRow.Hidden = True
        ElseIf Range("B163").Value > 0 Then
            Rows("164:169").EntireRow.Hidden = True
            Rows("162:163").EntireRow.Hidden = False
            Rows("170:171").EntireRow.Hidden = False
        ElseIf Range("B164").Value > 0 Then
            Rows("164:171").EntireRow.Hidden = False
            Rows("152").EntireRow.Hidden = False
            Rows("163").EntireRow.Hidden = True
        Else
            Rows("162:171").EntireRow.Hidden = False
        End If
0
 

Author Comment

by:Boston617
ID: 40525385
You're right! darn typos
Thank you!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40525595
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

860 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