Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the first character in a range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the first character in a range
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 -- Gary "John LR" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the first character in a range
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the first character in a range
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the first character in a range
"Héctor Miguel" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the first character in a range
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum the first character in a range
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Specific Character(s) In A Range? | Excel Discussion (Misc queries) | |||
Character Count Range of Cells | Excel Discussion (Misc queries) | |||
Character Count Range of Cells | Excel Discussion (Misc queries) | |||
How find character position # in string from right end? Or how get range row num | Excel Programming | |||
How can I insert a space before the last character in a range of cells | Excel Programming |