VBA Sum Product

[John L]

G'day everbody Help!
A B C D E
3A 4E 5A 6D Answer 18

On this row the procedure below works a treat,what I am trying for is to Sum
only the cells with "a" in mid(myrange1,2,1) Answer 8.Have tried a multitude
of different formulas but alas I admit defeat. Our dog will be gratefull for
any help as he cops my bad mood when it does not work. Regards John

Private Sub UserForm_Activate()
Dim myrange1 As Range
Dim a As Variant
Set myrange1 = Range(Selection.EntireRow.Cells(1, 4), Selection)
a = Evaluate("sumproduct(--left(0&" & myrange1.Address & ",2))")
msgbox a
end sub

 

[Chip Pearson]

You can do it with a simple function in VBA:

Function AddUp(RR As Range, Alpha As String) As Long
Dim Total As Long
Dim R As Range
For Each R In RR.Cells
If Len(R.Text) = 2 Then
If StrComp(Right(R.Text, 1), Alpha, vbTextCompare) = 0
Then
Total = Total + CLng(Left(R.Text, 1))
End If
End If
Next R
AddUp = Total
End Function

You can call this from a cell with

=AddUp(A11,"A")

or from other VBA code with

Dim Res As Long
Res = AddUp(Range("A11"),"A")

RR is the range of cells to examine and Alpha is the character to test
for (case insensitive -- change vbTextCompare to vbBinaryCompare if
you need case sensitivity).

I hope this makes your dog happy.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[John L]

Thanks Chip but my excel does not recognise AddUp ?

I am at work so dog is safe
Regards John

 

[Dave Peterson]

Did you put Chip's code for that =AddUp() UDF in a general module in that
workbook's project?

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Debra Dalgleish has some getstarted instructions for userforms at:
http://contextures.com/xlUserForm01.html

 

[Chip Pearson]

You need to put the function code in a standard VBA module. Open the
VBA editor (ALT F11), then open the Project window (CTRL R) if it is
not already visible (typically docked on the left side of the screen).
Select your workbook project in the TreeView in the Project window,
then go to the Insert menu and choose "Module". This will create a new
code module named "Module1" in your workbook project. Paste the AddUp
code in that module and the close the editor and return to Excel. With
the function code in a module, you can call it from a worksheet cell
with


=AddUp(A11,"A")

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[John L]

Thanks Chip, Being self taught I have never used Functions and what you have
explained is great and works well ,and can be used in a few other sheets
that I have done.But in this sheet I need the answer in a label in a
userform, have tried to use your worksheet function in the userform but no go.
Please keep the answers rolling, the dog is happy.
Regards John

 

[John L]

Chip no need to answer I found this in your other answer
or from other VBA code with
Dim Res As Long
Res = AddUp(Range("A11"),"A")

And this is great Many Many Thanks and Jack(the Dog) is Very Very Happy
Regards John