Boston617
asked on
code stops working near (but not at) end
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
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
Martin Liss
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.
Better yet would be to attach your workbook if it doesn't contain sensitive information.
ASKER
Hi Martin,
How would I attach my workbook?
How would I attach my workbook?
When you create a post you'll see this. Click it, then click the 'Choose File' button, followed by the green 'Upload File' button.
ASKER
Below is my workbook, I put a $1 value on a few states to show how it should appear
ASKER
Given the sheet with the data you posted, what is it doing wrong?
ASKER
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
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
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?
ASKER
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
The code doesn't work even if both B203 and B204 = 0, the rows don't collapse as they should
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.
ASKER
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
I also really want to do anything manual (clicking on rows, not inputing data) when I work on this
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You're right! darn typos
Thank you!
Thank you!
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
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014