OK, here's an example:
In column A is the case number, in B is the value, in C is the POS/neg (0/1) interpretation, and in D is the "copy, paste special, values" of C.
The equation in C is:
=IF(B1="","",IF(B1<0.4,0,1))
In columns ABCD are the below data (remember, column C is the above equation and column D is the "copy-paste special-values" from C).
1 0.0117 0 0
2 -0.0322 0 0
3 2.6209 1 1
4 2.6154 1 1
5 0.4346 1 1
6 0 0 0
7 0.0058 0 0
8 0.5621 1 1
9 0.9669 1 1
10 0.017 0 0
11 0.2445 0 0
12 0.0497 0 0
13 2.0841 1 1
14 0.203 0 0
15 0.7908 1 1
16 0.1667 0 0
17 -0.0497 0 0
18 1.4326 1 1
19 0.9499 1 1
20 0.3398 0 0
21
22 0.0283 0 0
23 -0.3092 0 0
24 2.4148 1 1
25 1.9118 1 1
26 2.4769 1 1
27 0.0261 0 0
28 0.0401 0 0
29 0.0752 0 0
30 0.8747 1 1
31 0.2843 0 0
32 -0.0396 0 0
33 -0.0117 0 0
34 0.0283 0 0
35 2.8764 1 1
36 0.0453 0 0
37 1.761 1 1
38 0.2089 0 0
39 0.2897 0 0
40 0.0594 0 0
41 0.3158 0 0
42 0.1554 0 0
43 -0.0057 0 0
44 0.9739 1 1
45 1.7973 1 1
46 0.5213 1 1
47 2.4174 1 1
48 0.7255 1 1
49 0.9695 1 1
50 0.761 1 1
51 0.1337 0 0
52
53 0.0226 0 0
54
55 1.507 1 1
56 1.5852 1 1
57 2.7304 1 1
58 0.8092 1 1
59 1.7608 1 1
60 1.3464 1 1
61 -0.0622 0 0
62 0.8324 1 1
63 -0.0141 0 0
64 0.7964 1 1
65 0.2582 0 0
66 1.8132 1 1
67 1.5496 1 1
68 -0.038 0 0
69 0.0632 0 0
70 0.2882 0 0
71 0.8824 1 1
72 0.2316 0 0
73 1.1978 1 1
74 0.0263 0 0
75 0.0057 0 0
76 0.2112 0 0
77 1.5043 1 1
78 0.3434 0 0
79 1.1699 1 1
80 0.8333 1 1
81 2.2468 1 1
82 0.1047 0 0
83 0.2306 0 0
84 0.0736 0 0
85 0.3399 0 0
86 1.3654 1 1
87 0.6566 1 1
88 0.2335 0 0
89
90 0.0205 0 0
91 0.0877 0 0
92
93 2.5934 1 1
94 0.0566 0 0
95 0.2386 0 0
96 0.6972 1 1
97 0.4216 1 1
98 0.1003 0 0
99 0.7201 1 1
100 -0.0029 0 0
101 2.3736 1 1
102 0.0322 0 0
103 -0.0255 0 0
104 -0.0707 0 0
105 -0.0509 0 0
106 2.6051 1 1
107
108 1.7451 1 1
109 0.4485 1 1
110 1.0967 1 1
111 -0.0205 0 0
112 0.0948 0 0
113 0.8728 1 1
114 2.3428 1 1
115 -0.0146 0 0
116 2.3517 1 1
117 0.5523 1 1
118 -0.0424 0 0
119 0.0175 0 0
120 1.402 1 1
121 2.0204 1 1
122 1.7214 1 1
123 0.683 1 1
124 0.6699 1 1
125 -0.0058 0 0
126 -0.0205 0 0
127 0.7386 1 1
128 0.0797 0 0
129 -0.0085 0 0
130 0.1961 0 0
131 0.9281 1 1
132 -0.1608 0 0
133 1.7608 1 1
134 0.1797 0 0
135 2.7527 1 1
136 0.3609 0 0
137 0.3599 0 0
138 0.0057 0 0
139 1.218 1 1
140 1.0967 1 1
141 0.4615 1 1
142 0.017 0 0
143 0.2614 0 0
144 0.193 0 0
145 0.0824 0 0
146 -0.0481 0 0
147 0.6845 1 1
148 1.5238 1 1
149 0.614 1 1
150 2.5 1 1
151 0.2288 0 0
152 1.2061 1 1
153 1.0305 1 1
154 0.8244 1 1
155 0.0906 0 0
156 2.8495 1 1
157 0.5599 1 1
158 0.0679 0 0
159 -0.0201 0 0
160 0.1754 0 0
161 2.2206 1 1
162 0.6111 1 1
163
164 0.3383 0 0
165 1.3562 1 1
166 0.2145 0 0
167 0.6242 1 1
168 -0.038 0 0
169 -0.0368 0 0
170 2.3797 1 1
171 -0.0453 0 0
172 1.4427 1 1
173 0.2647 0 0
174 0.5191 1 1
175 0.1281 0 0
176 -0.0198 0 0
177 -0.0526 0 0
178 1.1018 1 1
179 -0.0146 0 0
180 -0.0351 0 0
181 -0.0146 0 0
182 0.0306 0 0
183 0.0677 0 0
184 0.2381 0 0
185 0.25 0 0
186 1.112 1 1
187 2.5064 1 1
188 0.7068 1 1
189 0.1328 0 0
190 0.1078 0 0
191 0.2682 0 0
192 0.9248 1 1
193 0.0819 0 0
194 0.2941 0 0
195 2.2832 1 1
196 0.549 1 1
197 0.116 0 0
198 0.0339 0 0
199 2.1501 1 1
200 0.0947 0 0
201 0.3944 0 0
202 0.6463 1 1
203 1.7536 1 1
204 0 0 0
205 0.4336 1 1
206 2.2772 1 1
207 0.369 0 0
208 -0.0614 0 0
209 -0.0113 0 0
210 0.1016 0 0
211 0.8117 1 1
212 -0.0234 0 0
213 -0.038 0 0
214 -0.0117 0 0
215 0.0817 0 0
216 1.6621 1 1
217 0.8856 1 1
218 1.3995 1 1
219 0.2417 0 0
220 1.0276 1 1
221 0.0234 0 0
222 0.4423 1 1
223 -0.0058 0 0
224 0.3033 0 0
225 1.5293 1 1
226 0.6896 1 1
227 2.0738 1 1
228 0.5237 1 1
229 -0.0085 0 0
230 1.772 1 1
231
232
233 0.2088 0 0
234 0 0 0
235 0.0526 0 0
236 0.634 1 1
237 1.6967 1 1
238 0.0497 0 0
239 2.7303 1 1
240 0.6236 1 1
241 0.5288 1 1
242 0.1275 0 0
243 0.0198 0 0
244 -0.0058 0 0
245 1.989 1 1
246 0.183 0 0
247 0.0028 0 0
248 0.1671 0 0
249 0.8117 1 1
250 0.0251 0 0
251 0.4444 1 1
252 0.2506 0 0
253 0.2431 0 0
254 0.0752 0 0
255 1.7684 1 1
256 0.2353 0 0
257 3.1621 1 1
258 0.2431 0 0
259 0.0451 0 0
260 0.6768 1 1
261 0.0339 0 0
262 -0.0789 0 0
263 0.0451 0 0
What I need is a macro that I can start from anywhere in column C; that, from that relative point, goes on down the remaining cells in columns C, does a "copy-paste special-values" to column D (from C) and checks each cell in column D (depending on the criteria specified in another cell - in this case, I'd want it to find all blank cells, but may also want the option of having it instead find and delete all the cells with a 1 or zero) and then delete the contents of those blank cells...and it needs to loop through the column until it hits the bottom.
If you are wondering why I'd like it to delete the contents of an already blank cell, here's why: I'm trying to figure out a way to deal with the graphing problem in an automated way described at the following previous post...
http://www.mrexcel.com/board/viewtopic.php?topic=5082&forum=2
Thats one example. I can provide other examples for the other macros needed in the original post.
When I used to use Lotus 1-2-3, I could write a set of Lotus macros all tied together by a menu bar to do this (and all the things described in the original post). But since I converted to Excel, I'm trying to be able to do these same things in Excel with VBA that I was able to do in Lotus. I'm guessing that most if not all things I used to be able to do in Lotus can be done in Excel and more many times over, so I'm looking for some help to tap into these capabilities in Excel.
Any help is appreciated.
Dale