Removing concatenates for VBA calculs
 

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

Removing concatenates for VBA calculs
 

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" wrote in
message ...

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

Removing concatenates for VBA calculs
 

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

Have a good day!

Werne

--
Werne
-----------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...fo&userid=2430
View this thread: http://www.excelforum.com/showthread.php?threadid=38319