Sum & average values in range according to text in another column ?

[Lukael]

Hi,


I have a drop-down list wtih in column A (text values "P","R","G" etc.), and values in column B.

Can somebody show me how to write VBA code that will SUM all values with same text in column A, and then display average result on Userform's textbox ?

Example:

col A col B

"P" 1234 result = average of those two values ?
"P" 2334


Thanks for any help !

I attached sample !

[walruseggman]

Here you go.

Please note, your TextBox1 MultiLine property is set to False, you'll want to change that to True if you want each average on its own line. Otherwise you'll just see a carriage return symbol between each average.

Please Login or Register  to view this content.

[Lukael]

Hi Walruseggman,

I tried your code, but strangely It doesn't work with other ranges. This is what I need :

Please Login or Register  to view this content.

It doesn't work, nothing displayed on Userform. How can I fix this ?

[walruseggman]

Here is your problem:

Please Login or Register  to view this content.

LastRow is used so that the code can find the last row used. That's why I have the syntax "A1:A" & LastRow. There's no number on that second A because that number is represented by LastRow.

You, however, have hard coded in a last row for the range. This is perfectly fine to do, it just means you don't need to use LastRow anymore.

So either do this, if you want to hard code in the range:

Please Login or Register  to view this content.

Or this, if you want the code to find the last used row (in column 5, aka column E, as you have the code now) every time the code is run:

Please Login or Register  to view this content.

Make sense?

[Lukael]

Thanks, now it works !

Yes, It makes sense, I made a mistake !