on 10th August 08 asked for help to sum first number in a range of
cells,Hector came up with this

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

And it works a treat, but I have been trying madly to get it to work using
the second letter in a cell in the range as a criterea for the sum to work.
Please Help or I shall start smoking again. Regards John L

You need to use MID instead of Left to get the middle of a string. Mid
requires the start position of the character and the number of characters.

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

"John LR" wrote:

on 10th August 08 asked for help to sum first number in a range of
cells,Hector came up with this

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

And it works a treat, but I have been trying madly to get it to work using
the second letter in a cell in the range as a criterea for the sum to work.
Please Help or I shall start smoking again. Regards John L

Thanks for the answer Joel'
What I am trying to do is sum the left single number in a cell in a range ,
by using the mid(2,1) in the same cell as the criteria, eg if myrange1 cells
contain 2a.4a.5b. and the criteria is "a" then the answer is 6

"Joel" wrote:

You need to use MID instead of Left to get the middle of a string. Mid
requires the start position of the character and the number of characters.

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

"John LR" wrote:

on 10th August 08 asked for help to sum first number in a range of
cells,Hector came up with this

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

And it works a treat, but I have been trying madly to get it to work using
the second letter in a cell in the range as a criterea for the sum to work.
Please Help or I shall start smoking again. Regards John L

the VALUE on a worksheet works different then the VAL in VBA. The VBA
function will remove the number from the rest of the string.

Set myrange1 = Range(Selection.EntireRow.Cells(1, 1), Selection)
If Mid(myrange1, 2, 1) = "a" Then

Total = 0
For Each num In myrange1
Total = Total + Val(num)
Next num

MsgBox Total
End If
End Sub


"John LR" wrote:

Thanks for your reply Joel ,but I am trying to use the second letter in the
range cells as the criteria to sum the first number along the line of

Set myrange1 = Range(Selection.EntireRow.Cells(1, 1), Selection)
If mid(myrange1,2,1)="a" Then
MsgBox Evaluate("sumproduct(--left(0&" & myrange1.Address & ",2))")
End if
So if A1=5A, B1=6A,C1=5B Answer =11
Hope This is clear and some one can help. Regards John LR

"Joel" wrote:

You need to use MID instead of Left to get the middle of a string. Mid
requires the start position of the character and the number of characters.

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

"John LR" wrote:

on 10th August 08 asked for help to sum first number in a range of
cells,Hector came up with this

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

And it works a treat, but I have been trying madly to get it to work using
the second letter in a cell in the range as a criterea for the sum to work.
Please Help or I shall start smoking again. Regards John L