i am currently working a code to add the numerical parts of strings such as
"M4G3 M4P0". A problem arises however in my code when the string contains a
double digit number(ex. M4G3 M12P0). My code will recognize this as a two
individual single digit numbers (12= 1 and 2). Any suggestions? Here is the
code I'm using.
**********
Sub Sumcharacters()
Dim i As Long, s As String
Dim lsum As Long
For i = 1 To Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
lsum = lsum + CLng(s)
End If
Next
ActiveCell.Offset(0, 1).Value = lsum
End Sub

try this

Dim i As Long, s As String
Dim lsum As Long
i = 1
while i <= Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
Isum = 0
if IsNumeric(s) then

while IsNumeric(s)
lsum = (10 *lsum) + CLng(s)
i = i + 1
loop
else
i = i + 1
end if
loop
ActiveCell.Offset(0, 1).Value = lsum
End Sub

"DB" wrote:

i am currently working a code to add the numerical parts of strings such as
"M4G3 M4P0". A problem arises however in my code when the string contains a
double digit number(ex. M4G3 M12P0). My code will recognize this as a two
individual single digit numbers (12= 1 and 2). Any suggestions? Here is the
code I'm using.
**********
Sub Sumcharacters()
Dim i As Long, s As String
Dim lsum As Long
For i = 1 To Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
lsum = lsum + CLng(s)
End If
Next
ActiveCell.Offset(0, 1).Value = lsum
End Sub

When I use this, I get an error message for an invalid outside procedure. It
highlights "i=1" in the third line with the error message

"Joel" wrote:

try this

Dim i As Long, s As String
Dim lsum As Long
i = 1
while i <= Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
Isum = 0
if IsNumeric(s) then

while IsNumeric(s)
lsum = (10 *lsum) + CLng(s)
i = i + 1
loop
else
i = i + 1
end if
loop
ActiveCell.Offset(0, 1).Value = lsum
End Sub

"DB" wrote:

i am currently working a code to add the numerical parts of strings such as
"M4G3 M4P0". A problem arises however in my code when the string contains a
double digit number(ex. M4G3 M12P0). My code will recognize this as a two
individual single digit numbers (12= 1 and 2). Any suggestions? Here is the
code I'm using.
**********
Sub Sumcharacters()
Dim i As Long, s As String
Dim lsum As Long
For i = 1 To Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
lsum = lsum + CLng(s)
End If
Next
ActiveCell.Offset(0, 1).Value = lsum
End Sub

I sent an updated verion of this file in a second posting. Use the newer
program because it has some improvements. compile the program before you use
it. In VBA window under debug select compile. check for compiling errrors.
I = 1 as no reference by itself to a outside procedure. Ther must be other
compiling errors.

"DB" wrote:

When I use this, I get an error message for an invalid outside procedure. It
highlights "i=1" in the third line with the error message

"Joel" wrote:

try this

Dim i As Long, s As String
Dim lsum As Long
i = 1
while i <= Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
Isum = 0
if IsNumeric(s) then

while IsNumeric(s)
lsum = (10 *lsum) + CLng(s)
i = i + 1
loop
else
i = i + 1
end if
loop
ActiveCell.Offset(0, 1).Value = lsum
End Sub

"DB" wrote:

i am currently working a code to add the numerical parts of strings such as
"M4G3 M4P0". A problem arises however in my code when the string contains a
double digit number(ex. M4G3 M12P0). My code will recognize this as a two
individual single digit numbers (12= 1 and 2). Any suggestions? Here is the
code I'm using.
**********
Sub Sumcharacters()
Dim i As Long, s As String
Dim lsum As Long
For i = 1 To Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
lsum = lsum + CLng(s)
End If
Next
ActiveCell.Offset(0, 1).Value = lsum
End Sub