Dump macro result into cell

[Guest]

I currently am running a macro that sums the numerical values from a chain of
numbers and letters. What can I add to the macro to have the macro dump the
sum from the chain into the cell immediately to its right? I'd like this to
work for each cell in my column.

 

[JE McGimpsey]

One way:

Dim dSum As Double
Dim rCell As Range

With Sheets("Sheet1")
For Each rCell in .Range("A1:A" _
.Range("A" & .Rows.Count).End(xlUp).Row)

dSum = <your code here>

rCell.Offset(0, 1).Value = dSum
Next rCell
End With

 

[Guest]

This is the code im currently 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
MsgBox lsum
End Sub
*******************
I need to add something, so the sum from this code will be entered in the
cell to right of the 'activecell'
I am having troubles getting the code you suggested to fit with the code i
am currently using

Thanks for all the help

 

[Dave Peterson]

You could make it work like a worksheet function:

Option Explicit
Function SumCharacters(rng As Range) As Long

Dim i As Long
Dim s As String
Dim lSum As Long
Dim myCell As Range

lSum = 0
For Each myCell In rng.Cells
For i = 1 To Len(myCell.Value)
s = Mid(myCell.Value, i, 1) 'mycell.text if it's formatted
If IsNumeric(s) Then
lSum = lSum + s
End If
Next i
Next myCell

SumCharacters = lSum
End Function

then you could use:
=sumcharacters(a1)
or even
=sumcharacters(a1:b9)