PDA

View Full Version : Need Help on a macro...kind of desperate!



Beentrill
10-04-2014, 12:12 PM
Hi Guys,

Kind of new to vba in excel. Im working with Excel 2010.

In the attached workbook, you will see Sheet2 that stores some user names. In Column D, each user will have a box score rating which is set up as a string. There are altogether 7 possible box score ratings that a user can have.

I need to create a macro that can put all of the unique box score ratings into an array (7 columns) into another worksheet, then sum up how many users there are with each box score.

Can anyone online right now help me?

Beentrill
10-04-2014, 01:57 PM
Any help will be appreciated. I don't need someone to write the code for me. If you could just link an example or tutorial I can look at.

Thanks,

SamT
10-04-2014, 06:13 PM
I am in the middle of reinstalling Windows, so I can't open the attachment. However if all you really need is the different Box score totals, I would, in column "A" list the seven possibles, then in Column "B" use a COUNTIF Formula on the other Sheets Boxscores (Column "D") refering to the items in the list in Column "A" as Criteria.

B1 Formula is =COUNTIF(Sheet2!D:D, A1)
B2 Formula is =COUNTIF(Sheet2!D:D, A2)
Etc

Bob Phillips
10-05-2014, 01:04 AM
I would use SamT suggestion, but wrap it in a macro so as to make it replayable and cater for any additions


Sub AddTotals()
Dim lastrow As Long

With Worksheets("Sheet3")

Worksheets("Sheet2").Columns("D:D").Copy .Range("A1")
.Rows(1).Delete Shift:=xlUp

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1").Resize(lastrow).RemoveDuplicates Columns:=1, Header:=xlYes
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2").Resize(lastrow - 1).FormulaR1C1 = "=COUNTIF(Sheet2!C[2],RC[-1])"
End With
End Sub