Help needed (Automatic script function)

Hi there,

I have a slight problem and I hope that the people at this forum can give me a hand.

I have a database which has over 200 pages and I would need a script which could do the following to ease my
work;

http://img233.imageshack.us/img233/4388/excel6wg.jpg

I need the script to add the value in the E column (the way I show within the brackets). The script should ignore letters, commas points etc, and just add up the numbers to a 2 digit value, placeing the answer in the H column.

I would greatly appreciate if someone could lend me a helping hand with this, since I have to add the up manually until now... Thats what I get for not knowing excel

thnx for your time,

Soul.

For a value in A1....
B1: =SUMPRODUCT(IF(--ISNUMBER(--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)))

Note: commit that array formula by holding down [Ctrl]+[Shift] and press [Enter]

Does that help?

Regards,
Ron

Ron,

Wow! -- I thought this needed to be scripted.

Art

"Ron Coderre" wrote:

>
> For a value in A1....
> B1:
> =SUMPRODUCT(IF(--ISNUMBER(--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)))
>
> Note: commit that array formula by holding down [Ctrl]+[Shift] and
> press [Enter]
>
> Does that help?
>
> Regards,
> Ron
>
>
> --
> Ron Coderre
> ------------------------------------------------------------------------
> Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
> View this thread: http://www.excelforum.com/showthread...hreadid=499350
>
>

Yo ppl,

First of all, thx for such a fast reply. I tried the formula, although since I have the Spanish version of Excel, I doubt the formulas could work since it seems, that even the commands have been translated. I have to fish out the english version and try it asap. When I did add the fomula, it asked me for #NAME? and didnt seen to work. Im sorry about this, but Im a total noob in Excel but Im forced to use it ...... Ill give it a try again asap, and Ill msg back for help if it doesnt work. Thx Art / Ron.

Soul.

Soul,

Try the following script -- this one is written for row 1 -- you'd have to
loop to get the rest of the rows.

Sub temp()
Dim i As Integer
Dim j As Integer
For i = 1 To Len(Cells(1, 6))
If IsNumeric(Cells(1, 6)) Then
x = x + Val(Mid(Cells(1, 6), i, 1))
End If
Next i
Cells(1, 8) = x
End Sub

Art
"Soul" wrote:

>
> Hi there,
>
> I have a slight problem and I hope that the people at this forum can
> give me a hand.
>
> I have a database which has over 200 pages and I would need a script
> which could do the following to ease my
> work;
>
> http://img233.imageshack.us/img233/4388/excel6wg.jpg
>
> I need the script to add the value in the E column (the way I show
> within the brackets). The script should ignore letters, commas points
> etc, and just add up the numbers to a 2 digit value, placeing the
> answer in the H column.
>
> I would greatly appreciate if someone could lend me a helping hand with
> this, since I have to add the up manually until now... Thats what I get
> for not knowing excel
>
> thnx for your time,
>
> Soul.
>
>
> --
> Soul
> ------------------------------------------------------------------------
> Soul's Profile: http://www.excelforum.com/member.php...o&userid=30265
> View this thread: http://www.excelforum.com/showthread...hreadid=499350
>
>