Solved

code stops working near (but not at) end

Posted on 2014-12-30
17
47 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 45

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 45

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
 
LVL 45

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 45

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

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 45

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 45

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 45

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 45

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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

16 Experts available now in Live!

Get 1:1 Help Now