finding and displaying data

[red95]

this is what i am trying to do

8010 E Raymond St 10107
8020 E Raymond St 10107
8040 E Raymond St 10107
8042 E Raymond St 10107
8106 E Raymond St 10107
8124 E Raymond St 10107
8130 E Raymond St 10107
8140 E Raymond St 10107
2207 Raymond Park Dr 10107
2211 Raymond Park Dr 10107
2215 Raymond Park Dr 10107
2219 Raymond Park Dr 10107
2223 Raymond Park Dr 10107
2247 Layton Park Ln 10107
2253 Layton Park Ln 10107

data starts at A10 on the data sheet
i want to display the data on the main sheet
15 rows at a time
on main sheet A5 will be input of 10107 or 78502 or 86011
lets say i pick 10107 now the data should be A10-A25 displayed then halted waiting for button press then A26-A41 and so on till e = 999
now if A5 input on main sheet is 78502 then the data should display row
A51
A52
A54
A53
A55
A58
A56
A57
A59
A60
A61
till e = 999
and as you can see data is displayed in numbered order of D column and when e column hits 999 it stops

(data) sheet

A B C D E
10 8010 E Raymond St 10107 1
11 8020 E Raymond St 10107 2
12 8040 E Raymond St 10107 3
13 8042 E Raymond St 10107 4
14 8106 E Raymond St 10107 5
15 8124 E Raymond St 10107 6
16 8130 E Raymond St 10107 7
17 8140 E Raymond St 10107 8
18 2207 Raymond Park Dr 10107 9
19 2211 Raymond Park Dr 10107 10
20 2215 Raymond Park Dr 10107 11
21 2219 Raymond Park Dr 10107 12
22 2223 Raymond Park Dr 10107 13
23 2247 Layton Park Ln 10107 14
24 2253 Layton Park Ln 10107 15
25 2259 Layton Park Ln 10107 16
26 2265 Layton Park Ln 10107 17
27 2271 Layton Park Ln 10107 18
28 2277 Layton Park Ln 10107 19
29 2283 Layton Park Ln 10107 20
30 2303 Layton Park Ln 10107 21
31 2309 Layton Park Ln 10107 22
32 2313 Layton Park Ln 10107 23
33 2319 Layton Park Ln 10107 24
34 2329 Layton Park Ln 10107 25
35 2335 Layton Park Ln 10107 26
36 2341 Layton Park Ln 10107 27
37 2347 Layton Park Ln 10107 28
38 2353 Layton Park Ln 10107 29
39 2356 Layton Park Ln 10107 30
40 2350 Layton Park Ln 10107 31
41 2344 Layton Park Ln 10107 32
42 2338 Layton Park Ln 10107 33
43 2334 Layton Park Ln 10107 34
44 2330 Layton Park Ln 10107 35
45 2326 Layton Park Ln 10107 36
46 2320 Layton Park Ln 10107 37
47 2316 Layton Park Ln 10107 38
48 2312 Layton Park Ln 10107 39
49 2308 Layton Park Ln 10107 40
50 2302 Layton Park Ln 10107 41 999
51 2284 Layton Park Ln 78502 1
52 2278 Layton Park Ln 78502 2
53 2272 Layton Park Ln 78502 4
54 2266 Layton Park Ln 78502 3
55 2260 Layton Park Ln 78502 5
56 2252 Layton Park Ln 78502 7
57 2273 Raymond Park Dr 78502 8
58 2281 Raymond Park Dr 78502 6
59 2285 Raymond Park Dr 78502 9
60 2289 Raymond Park Dr 78502 10
61 2293 Raymond Park Dr 78502 11 999
62 2292 Raymond Park Dr 10107 1
63 2288 Raymond Park Dr 10107 2
64 2284 Raymond Park Dr 10107 3
65 2280 Raymond Park Dr 10107 4
66 2276 Raymond Park Dr 10107 5
67 2272 Raymond Park Dr 10107 6 999
68 2268 Raymond Park Dr 86011 1
69 2264 Raymond Park Dr 86011 2
70 2260 Raymond Park Dr 86011 3
71 2256 Raymond Park Dr 86011 4 999
72 2250 Raymond Park Dr 10107 1
73 2246 Raymond Park Dr 10107 1
74 2242 Raymond Park Dr 10107 2
75 2238 Raymond Park Dr 10107 3
76 2234 Raymond Park Dr 10107 4
77 2230 Raymond Park Dr 10107 5
78 2226 Raymond Park Dr 10107 6
79 2239 Salem Park Dr 10107 7
80 2245 Salem Park Dr 10107 8
81 2251 Salem Park Dr 10107 9
82 2257 Salem Park Dr 10107 10
83 2263 Salem Park Dr 10107 11
84 2269 Salem Park Dr 10107 12
85 2275 Salem Park Dr 10107 13
86 2279 Salem Park Dr 10107 14
87 2283 Salem Park Dr 10107 15
88 2289 Salem Park Dr 10107 16
89 2305 Salem Park Dr 10107 17
90 2311 Salem Park Dr 10107 18
91 2321 Salem Park Dr 10107 19
92 2327 Salem Park Dr 10107 20
93 2333 Salem Park Dr 10107 21
94 2339 Salem Park Dr 10107 22
95 2345 Salem Park Dr 10107 23
96 2349 Salem Park Dr 10107 24
97 2353 Salem Park Dr 10107 25
98 2359 Salem Park Dr 10107 26
99 2365 Salem Park Dr 10107 27
100 2373 Salem Park Dr 10107 28
101 2379 Salem Park Dr 10107 29
102 2385 Salem Park Dr 10107 30
103 2403 Salem Park Dr 10107 31
104 2407 Salem Park Dr 10107 32
105 2411 Salem Park Dr 10107 33
106 2415 Salem Park Dr 10107 34
107 2423 Salem Park Dr 10107 35
108 2378 Salem Park Ct 10107 36
109 2372 Salem Park Ct 10107 37
110 2366 Salem Park Ct 10107 38
111 2360 Salem Park Ct 10107 39
112 2354 Salem Park Ct 10107 40
113 2350 Salem Park Ct 10107 41
114 2346 Salem Park Ct 10107 42
115 2340 Salem Park Ct 10107 43
116 2336 Salem Park Ct 10107 44
117 2332 Salem Park Ct 10107 45 999
118 2331 Salem Park Ct 86011 1
119 2335 Salem Park Ct 86011 2
120 2343 Salem Park Ct 86011 3
121 2351 Salem Park Ct 86011 4 999
122 2355 Salem Park Ct 10107 1
123 2361 Salem Park Ct 10107 2
124 2367 Salem Park Ct 10107 3
125 2371 Salem Park Ct 10107 4
126 2377 Salem Park Ct 10107 5
127 2328 Salem Park Dr 10107 6
128 2322 Salem Park Dr 10107 7
129 2318 Salem Park Dr 10107 8
130 2314 Salem Park Dr 10107 9
131 2310 Salem Park Dr 10107 10
132 2304 Salem Park Dr 10107 11
133 2288 Salem Park Dr 10107 12
134 2282 Salem Park Dr 10107 13
135 2276 Salem Park Dr 10107 14
136 2270 Salem Park Dr 10107 15
137 2264 Salem Park Dr 10107 16
138 2258 Salem Park Dr 10107 17
139 2248 Salem Park Dr 10107 18
140 2222 Raymond Park Dr 10107 19
141 2218 Raymond Park Dr 10107 20
142 2214 Raymond Park Dr 10107 21
143 2210 Raymond Park Dr 10107 22
144 8202 E Raymond St 10107 23
145 8723 E Raymond St 10107 24
146 8425 E Raymond St 10107 25
147 8723 E Raymond St 10107 26
148 8749 E Raymond St 10107 27
149 8801 E Raymond St 10107 28
150 8809 E Raymond St 10107 29
151 8819 E Raymond St 10107 30 999
152 8837 E Raymond St 78502 1
153 8880 E Raymond St 78502 2
154 8888 E Raymond St 78502 3 999
155 2150 S Post Rd 10107 1
156 2111 S Post Rd 10107 2
157 2107 S Post Rd 10107 3
158 2051 S Post Rd 10107 4
159 2041 S Post Rd 10107 5
160 2031 S Post Rd 10107 6
161 2021 S Post Rd 10107 7
162 2011 S Post Rd 10107 8
163 1940 S Post Rd 10107 9
164 1835 S Post Rd 10107 10
165 1919 S Post Rd 10107 11
166 1953 S Post Rd 10107 12 999
167 9015 E Troy Ave 86011 1
168 9030 E Troy Ave 86011 2
169 9035 E Troy Ave 86011 4
170 9055 E Troy Ave 86011 5
171 9040 E Troy Ave 86011 3
172 9104 E Troy Ave 86011 6
173 9103 E Troy Ave 86011 7
174 9119 E Troy Ave 86011 8
175 9116 E Troy Ave 86011 13
176 9124 E Troy Ave 86011 14
177 9135 E Troy Ave 86011 20
178 9130 E Troy Ave 86011 19
179 9142 E Troy Ave 86011 18
180 9201 E Troy Ave 86011 17
181 9202 E Troy Ave 86011 16 999

 

[red95]

windows 7 excel 2007

 

[Peter_SSs]

i want to display the data on the main sheet
15 rows at a time

lets say i pick 10107 now the data should be A10-A25 displayed then halted waiting for button press then A26-A41 and so on till e = 999

Some clarification is required.

1. How many rows to display at a time? The blue bit above says 15, but the red bits both indicate 16 rows?

2. To me your description would indicate that (for example) rows 62-67, 72-117, 122-151 and 155-166 would never get displayed.

That is because if 10107 is entered in A5 of the 'Main' sheet you describe showing 10107 rows in goups until 999 is reached in column E. Now for 10107, 999 occurs (first) in row 50, so the display stops there. Have I interpreted that correctly?

 

[red95]

15 or 16 rows dont matter just an example
ok to clarify it would display 15 rows then halt and wait for a button to be pressed when pressed is would display next 15 till e = 999 then halt when button is pressed again want 62 - 67 displayed and halt because e = 999 only 6 rows would have been displayed
when button is pressed again 72 - 87 then halted again waiting for button press and so on

one thing if you noticed column d has a number want the data displayed in that decending order and the order mite not be set in order
example
167 9015 E Troy Ave 86011 1
168 9030 E Troy Ave 86011 2
169 9035 E Troy Ave 86011 4
170 9055 E Troy Ave 86011 5
171 9040 E Troy Ave 86011 3
172 9104 E Troy Ave 86011 6
173 9103 E Troy Ave 86011 7
174 9119 E Troy Ave 86011 8
175 9116 E Troy Ave 86011 13
176 9124 E Troy Ave 86011 14
177 9135 E Troy Ave 86011 20
178 9130 E Troy Ave 86011 19
179 9142 E Troy Ave 86011 18
180 9201 E Troy Ave 86011 17
181 9202 E Troy Ave 86011 16 999
but we are working with 10107 not 86011 just telling you the data is not always in order i want displayed and my data has over 3000 rows i just showed an example

 

[Peter_SSs]

Well, that is quite an ask. However, it did intrigue me so I've had a go. I am not at all sure I have the most efficient method or the best programming technique but it seems to work. I just hope that I have interpreted your requirements correctly.

When you enter a value (eg 86011) in cell A5 of the 'Main' sheet it should show a section of data for that value. Instead of a button to show the next section (if there is one) just double click cell A5. Repeat that process to show successive sections. A message will appear when there is no more data to display.

To implement (test in a copy of your workbook) ..

1. Right click the 'Main' sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try entering/deleting values in cell A5 (or whatever cell is set at the top of the code for "IP").

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, DispDataRows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Private</SPAN> Disp(1 To Drows, 1 To 5)<br><SPAN style="color:#00007F">Private</SPAN> Dispdata<br><SPAN style="color:#00007F">Private</SPAN> rDisplay <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Private</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> IP <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A5" <SPAN style="color:#007F00">'<- Input cell</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> Drows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 15  <SPAN style="color:#007F00">'<- Display rows</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 10    <SPAN style="color:#007F00">'<- 1st row Data sheet</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> rIP <SPAN style="color:#00007F">As</SPAN> Range, a <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Orig<br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rIP = Range(IP)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, rIP) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rDisplay = rIP.Offset(1).Resize(Drows, 5)<br>        s = rIP.Value<br>        <SPAN style="color:#00007F">If</SPAN> Len(s) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            DispDataRows = 0<br>            <SPAN style="color:#00007F">With</SPAN> Sheets("Data")<br>                rws = .Range("A" & .Rows.Count).End(xlUp).Row - fr + 1<br>                <SPAN style="color:#00007F">With</SPAN> .Range("A" & fr).Resize(rws, 6)<br>                    Orig = .Value<br>                    .Columns(5).Insert<br>                    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> a <SPAN style="color:#00007F">In</SPAN> .Offset(, 5).Resize(, 1) _<br>                            .SpecialCells(xlCellTypeBlanks).Areas<br>                        <SPAN style="color:#00007F">With</SPAN> a<br>                            <SPAN style="color:#00007F">If</SPAN> .Cells(1, 1).Offset(, -3).Value = s <SPAN style="color:#00007F">Then</SPAN><br>                                rws = .Rows.Count + 1<br>                                x = x + 1<br>                                .Resize(rws).Offset(, -1).Value = x & s<br>                                DispDataRows = DispDataRows + rws<br>                            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Next</SPAN> a<br>                    <SPAN style="color:#00007F">If</SPAN> DispDataRows = 0 <SPAN style="color:#00007F">Then</SPAN><br>                        .Columns(5).Delete<br>                        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                        rIP.ClearContents<br>                        rDisplay.ClearContents<br>                        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>                        MsgBox "No " & s & " data to display"<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                        .Sort Key1:=.Cells(1, 5), Order1:=xlAscending, _<br>                            Header:=xlNo, OrderCustom:=1, MatchCase:=False, _<br>                            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal<br>                        .Resize(, 5).Sort Key1:=.Cells(1, 5), Order1:=xlAscending, _<br>                            Key2:=.Cells(1, 4), Order2:=xlAscending, _<br>                            Header:=xlNo, OrderCustom:=1, MatchCase:=False, _<br>                            Orientation:=xl<SPAN style="color:#00007F">To</SPAN>pToBottom, DataOption1:=xlSortNormal, _<br>                            DataOption2:=xlSortNormal<br>                        .Columns(5).Delete<br>                        Dispdata = .Resize(DispDataRows, 5).Value<br>                        .Value = Orig<br>                        i = 0<br>                        DisplayNextSection<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            rDisplay.ClearContents<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">If</SPAN> Target.Address(0, 0) = IP And Len(Target.Value) > 0 <SPAN style="color:#00007F">Then</SPAN><br>        Cancel = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> i = DispDataRows <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "No more " & s & " data to display"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            DisplayNextSection<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> DisplayNextSection()<br>    <SPAN style="color:#00007F">Dim</SPAN> k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Erase Disp<br>    <SPAN style="color:#00007F">Do</SPAN><br>        i = i + 1<br>        k = k + 1<br>        <SPAN style="color:#00007F">For</SPAN> j = 1 To 5<br>            Disp(k, j) = Dispdata(i, j)<br>        <SPAN style="color:#00007F">Next</SPAN> j<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> k = Drows <SPAN style="color:#00007F">Or</SPAN> Disp(k, 5) = 999<br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    rDisplay.Value = Disp<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>