Sum the first character in a range

[John LR]

Could some body Please help.
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

 

[Gary Keramidas]

your example isn't very elaborate, as to if there are more rows or any other
factors

Sub test()
Dim i As Long
Dim n As Double
For i = 1 To 3
n = Left(Cells(1, i), 1) + n
Next
Range("e1").Value = n
End Sub

 

[Héctor Miguel]

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.

 

[Rick Rothstein \(MVP - VB\)]

As long as your numbers are whole numbers or, if they are floating point
numbers, as long as your decimal point is a dot, you can use this
function...

Function SumRow(RowNumber As Long) As Double
Dim C As Range
Dim X As Long
Dim LastCol As Long
With Worksheets("Sheet1")
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For X = 1 To LastCol
SumRow = SumRow + Val(.Cells(1, X).Value)
Next
End With
End Function

If there is the possibility that floating point values may be encountered
and the decimal point could be either a dot or a comma, then this variation
can be used...

Function SumRow(RowNumber As Long) As Double
Dim C As Range
Dim X As Long
Dim LastCol As Long
With Worksheets("Sheet1")
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For X = 1 To LastCol
SumRow = SumRow + Val(Replace(.Cells(1, X).Value, ",", "."))
Next
End With
End Function

Rick

 

[John LR]

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.

Thanks a million hector

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
thanks again John

 

[Héctor Miguel]

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.

 

[John LR]

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

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.