Removing concatenates for VBA calculs

[Werner]

Hi,

I would like suming numbers in cells. The only problem is that I concatenated them as followed : *546,23_$*
I put the underscore for empty space which is a character by itself. I just don't know how to use together Left and Right function to move from 1 space from left and to go down 2 spaces from the right at the same time to only obtain the number and do maths with them. I must not change the concatenated cells in that process.

When I concatenated the number and the *, it didn't keep the monatery format of the number. So I imitated the monetary format by this way. But I am opened to any bright ways to help me correct that matter too.


Thx!

Werner

[Tom Ogilvy]

Dim sStr as String
Dim cell as Range
Dim dblVal as double
dim dblSum as double
dblSum = 0
for each cell in selection
cell.activate
sStr = Right(ActiveCell.Value,Len(ActiveCell.Value)-1)
sStr = Left(sStr,len(activeCell.value)-3)
dblval = cdbl(sStr)
dblSum = dblSum + dblval
Next
msgbox dblSum

--
Regards,
Tom Ogilvy

"Werner" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I would like suming numbers in cells. The only problem is that I
> concatenated them as followed : *546,23_$*
> I put the underscore for empty space which is a character by itself. I
> just don't know how to use together Left and Right function to move
> from 1 space from left and to go down 2 spaces from the right at the
> same time to only obtain the number and do maths with them. I must not
> change the concatenated cells in that process.
>
> When I concatenated the number and the *, it didn't keep the monatery
> format of the number. So I imitated the monetary format by this way.
> But I am opened to any bright ways to help me correct that matter too.
>
>
>
> Thx!
>
> Werner
>
>
> --
> Werner
> ------------------------------------------------------------------------
> Werner's Profile:
http://www.excelforum.com/member.php...o&userid=24304
> View this thread: http://www.excelforum.com/showthread...hreadid=383194
>

[Werner]

Thanks for your help Tom! I no more stuck. Futhermore, I think it's a well written code. Good job!

Have a good day!

Werner