I am running a macro through one specific column of my spreadsheet. From cell
B4 to anywhere from B20 or higher, to be exact. I need to add something to my
code so that my macro will execute in each cell moving down that column until
it encounters the first blank cell. At that point it should stop altogether,
doing nothing with the blank cell.

Any suggestions?

Charles,
I have an entire column of cells. Each cell in this column contains a
string of alternating numbers and letters such as "M4P0 M3G4". The macro I am
currently using, adds the numeric parts of these cells and places the sum in
the cell directly to the right of the cell containing the string. I'd like to
add something to my macro in hopes that I could highlight the first cell in
the column of cells, start my macro with a button, and the macro would
evaluate the first cell, place it sum in the next column, then move down the
column repeating this task until it encounters the first cell that contains
no string of numbers ( a completely blank cell ). At this point, the macro
should recognize that the cell is blank and stop.

Example. Start Macro in column A4, macro places sum in B4. Macro moves down
to A5, evaluates A5, and places its sum in B5. Macro repeats this task until
first blank cell. If the first blank cell is A20, the macro will recognize
this, stop, and place nothing in cell B20.

Thanks
William

"Charles Chickering" wrote:

William, I'm not sure what your desired end result is. What are you trying to
accomplish and what giving you "inaccurate results"?
--
Charles Chickering

"A good example is twice the value of good advice."


"william" wrote:

I'm close, but I'm getting inaccurate results. Do you see any errors?
*****
Sub Sumcharacters()
Dim i As Long, s As String
Dim nSum As Long
Dim lSum As Long
i = 1
Do
Do While i <= Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
nSum = 0
Do While IsNumeric(s)
nSum = nSum * 10 + CLng(s)
i = i + 1
s = Mid(ActiveCell, i, 1)
Loop
lSum = lSum + nSum
Else
i = i + 1
End If
Loop
ActiveCell.Offset(0, 1).Value = lSum
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = 0
End Sub
*****

"Charles Chickering" wrote:

here's one simple way assuming your macro operates based on the active cell:
Sub YourSub()
Do
'YourStuff
ActiveCell.Offset(1).Select
Loop Until ActiveCell = ""
--
Charles Chickering

"A good example is twice the value of good advice."


"william" wrote:

I am running a macro through one specific column of my spreadsheet. From cell
B4 to anywhere from B20 or higher, to be exact. I need to add something to my
code so that my macro will execute in each cell moving down that column until
it encounters the first blank cell. At that point it should stop altogether,
doing nothing with the blank cell.

Any suggestions?

William, Here's a different approach to this:
Dim cnt As Long
Dim s As String
Dim s2() As String
Dim lSum As Long
Do
s = ActiveCell
For cnt = 65 To 90
s = Replace(s2, Chr(cnt), " ", , , vbTextCompare)
Next
s2 = Split(Trim(s))
lSum = 0
For cnt = 0 To UBound(s2)
lSum = lSum + CLng(s2(cnt))
Next
ActiveCell.Offset(, 1) = lSum
ActiveCell.Offset(1).Select
Loop Until ActiveCell = ""

I'm basically replacing all letters in the string with a space then
splitting the string and adding the numbers that are left in the array.
--
Charles Chickering

"A good example is twice the value of good advice."


"william" wrote:

Charles,
I have an entire column of cells. Each cell in this column contains a
string of alternating numbers and letters such as "M4P0 M3G4". The macro I am
currently using, adds the numeric parts of these cells and places the sum in
the cell directly to the right of the cell containing the string. I'd like to
add something to my macro in hopes that I could highlight the first cell in
the column of cells, start my macro with a button, and the macro would
evaluate the first cell, place it sum in the next column, then move down the
column repeating this task until it encounters the first cell that contains
no string of numbers ( a completely blank cell ). At this point, the macro
should recognize that the cell is blank and stop.

Example. Start Macro in column A4, macro places sum in B4. Macro moves down
to A5, evaluates A5, and places its sum in B5. Macro repeats this task until
first blank cell. If the first blank cell is A20, the macro will recognize
this, stop, and place nothing in cell B20.

Thanks
William

"Charles Chickering" wrote:

William, I'm not sure what your desired end result is. What are you trying to
accomplish and what giving you "inaccurate results"?
--
Charles Chickering

"A good example is twice the value of good advice."


"william" wrote:

I'm close, but I'm getting inaccurate results. Do you see any errors?
*****
Sub Sumcharacters()
Dim i As Long, s As String
Dim nSum As Long
Dim lSum As Long
i = 1
Do
Do While i <= Len(ActiveCell)
s = Mid(ActiveCell, i, 1)
If IsNumeric(s) Then
nSum = 0
Do While IsNumeric(s)
nSum = nSum * 10 + CLng(s)
i = i + 1
s = Mid(ActiveCell, i, 1)
Loop
lSum = lSum + nSum
Else
i = i + 1
End If
Loop
ActiveCell.Offset(0, 1).Value = lSum
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = 0
End Sub
*****

"Charles Chickering" wrote:

here's one simple way assuming your macro operates based on the active cell:
Sub YourSub()
Do
'YourStuff
ActiveCell.Offset(1).Select
Loop Until ActiveCell = ""
--
Charles Chickering

"A good example is twice the value of good advice."


"william" wrote:

I am running a macro through one specific column of my spreadsheet. From cell
B4 to anywhere from B20 or higher, to be exact. I need to add something to my
code so that my macro will execute in each cell moving down that column until
it encounters the first blank cell. At that point it should stop altogether,
doing nothing with the blank cell.

Any suggestions?