Hi, welcome to the board!
If you had your data looking something like this ...
Book1 |
---|
|
---|
| A | B | C | D |
---|
1 | 1 | | | |
---|
2 | 2 | | | |
---|
3 | 3 | | | |
---|
4 | 4 | | | |
---|
5 | 5 | 15 | | |
---|
6 | #NUM! | | | |
---|
7 | #NUM! | | | |
---|
8 | #NUM! | | | |
---|
9 | #NUM! | | | |
---|
10 | #NUM! | | | |
---|
11 | #NUM! | | | |
---|
12 | 6 | | | |
---|
13 | 7 | | | |
---|
14 | 8 | | | |
---|
15 | 9 | | | |
---|
16 | 10 | 40 | | |
---|
17 | | FormulaCheck: | VBA | |
---|
18 | | 55 | 55 | |
---|
|
---|
Then you could run a Standard procedure like this ...<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><SPAN style="color:#00007F">Sub</SPAN> AddMeUp()
<SPAN style="color:#00007F">Dim</SPAN> cel<SPAN style="color:#00007F">As</SPAN> Range, rng<SPAN style="color:#00007F">As</SPAN> Range, dest<SPAN style="color:#00007F">As</SPAN> Range, tmpCel<SPAN style="color:#00007F">As</SPAN> Range, wf
<SPAN style="color:#00007F">Set</SPAN> wf = Application.WorksheetFunction
<SPAN style="color:#00007F">Set</SPAN> dest = Range("C18")
<SPAN style="color:#00007F">Set</SPAN> rng = Range("A1", Range("A65536").End(xlUp))
<SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> cel<SPAN style="color:#00007F">In</SPAN> rng
<SPAN style="color:#00007F">If</SPAN> IsNumeric(cel.Value)<SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">If</SPAN> tmpCel<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">Set</SPAN> tmpCel = cel
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">Set</SPAN> tmpCel = Union(cel, tmpCel)
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> cel
dest.Value = wf.Sum(tmpCel)<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>
HTH