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