Thanks Hector this works great, the roster and I thank you a lot
regards John

"Héctor Miguel" wrote:

hi, John !

Set myrange1 = Range(Selection.EntireRow.Cells(1, 1), Selection)
MsgBox Evaluate("sumproduct(--left(" & myrange1.Address & "))")

this range formula works a treat with one small prob
if there is a empty cell in the range it rolls over
how do I fix this ...

add a "tricky" leading 0 -zero- and look for the 2 left-characters (i.e.)

MsgBox Evaluate("sumproduct(--left(0&" & myrange1.Address & ",2))")

hth,
hector.

__ OP __
hi, John !

I need to sum the first number in each cell of my Range EG:
A1 B1 C1
2a 6c 2we = 10

Dim myrange1 As Range
Dim a As Integer
Set myrange1 = Range(Selection.EntireRow.Cells(1, 1), Selection)
a = Application.WorksheetFunction.Sum(myrange1)
MsgBox a

Any help much appreciated

- a formula in your worksheet ?, try with...
=sumproduct(--left(a1:c1))

- something thru vba ?, try with...
msgbox [sumproduct(--left(a1:c1))]
msgbox evaluate("sumproduct(--left(a1:c1))")

- using variables for "the range" ?, try with...
msgbox evaluate("sumproduct(--left(" & selection.address & "))")

hth,
hector.