Calculating Totals based on the visible information on a filtered spreadsheet

[Timheatworks]

Hello,

I have created a spreadsheet that has employee scores on it for individual addresses. I created a button that adds the scores that are between 0 to 6, 7 to 8, and 9 to 10. and total these scores in those ranges on sheet 2.

For example Sheet 2 would show:
Scores
0 - 6 = 20
7 - 8 = 10
9 - 10 = 50

Now my problem lies in when I apply a filter to the spreadsheet to just bring up 1 individual employee or filter the spreadsheet by say the month of June my macro button still adds up the entire spreadsheet instead of what is visible after I apply the filter.

My question: Is there a way in my VB code to specify that I only want to calculate the scores that are visible once I apply the filter to the spreadsheet? The reason for this is so I can quickly filter by month or employee and using the same macro button I can quickly see what that totals were so I don't have to write individual filters in my code to do this.

Thank you for any help you can provide me with this matter.

Tim

[xladept]

You could restrict your tally procedure to:

Please Login or Register  to view this content.

[Timheatworks]

Here is the code I am using for my macro button. Basically it starts at the top field on the spreadsheet and works its way down until it runs into an empty field (which occurs only at the bottom of my inputted data) and adds 1 point to which ever category the score falls into. It then just updates the fields on sheet 3 so I can see the totals. I add new rows on a daily basis as more scores come in so it is constantly growing in rows.

Sub Scores()

Dim Counter
Dim Promoter
Dim Passive
Dim Detractor

Dim Tester

Counter = 3
Promoter = 0
Passive = 0
Detractor = 0
Tester = 0

'Scanning through work sheet until end of applicable values is reached which would be an empty field

Do While Worksheets("Sheet1").Cells(Counter, 4).Value <> ""

'This is just being used for debugging purposes

Tester = Worksheets("Sheet1").Cells(Counter, 4).Value

' Determining which range the scores fall into and incrementing the range total by 1

If Worksheets("Sheet1").Cells(Counter, 4).Value > 8 Then

Promoter = Promoter + 1
Counter = Counter + 1

ElseIf Worksheets("Sheet1").Cells(Counter, 4).Value > 6 Then

Counter = Counter + 1
Passive = Passive + 1

Else

Counter = Counter + 1
Detractor = Detractor + 1

End If



Loop

'Updating Scores on Sheet 3

Worksheets("Sheet3").Cells(4, 2).Value = Promoter
Worksheets("Sheet3").Cells(5, 2).Value = Passive
Worksheets("Sheet3").Cells(6, 2).Value = Detractor


End Sub

[Timheatworks]

I see where I messed up. I made the mistake of thinking Excel reassigned cell locations when you filtered it but cell D113 will always be Cell D113 even if its the only cell showing on the filter. I will have to rethink this from scratch. Thanks for your replys.

[xladept]

You could assign the visible cells to an array in core and run your tally on that

Please Login or Register  to view this content.