VBA Excel If Statement

[mulholm]

I need a bit of help writing some code.

I have a spreadsheet to track stats of a teams performance however the system that is used to generate the stats does not produce a team total so everyday i have to go in and manually add the formula.

I was going to just do a basic =sum formula to total up the data however the "Team" stats will appear in a different row each day depending on how many people have been in that day.

What i want to achieve is something along the lines of a "If cell = Team Total then sum of the above data"

Hope that makes sense.

cheers

 

[JustynaMK]

Hiya mulholm,

Assuming that your table has the following structure:

Column A || Column B
a || 1
b || 2
c || 3
d || 4
e || 5
f || 6
g || 7
Team Total

Then you can use the following code:

Option Explicit


Sub TeamTotal()
    Dim lngRow              As Long
    
    With Sheet1
        'find "Team Total" position
        lngRow = [COLOR=#ff0000]Application.WorksheetFunction.Match("Team Total", .Range("A:A"), 0)[/COLOR]
        'apply the sum formula
       [COLOR=#0000ff] .Range("B" & lngRow).Formula = "=SUM[/COLOR]([COLOR=#ff8c00]B1:B" & lngRow - 1 & ")"[/COLOR]
    End With


End Sub

It will find the position of "Team Total" in column A (in our example, the result is row 8) and create SUM formula in cell B8 which sums all values between B1 and B7.

Let me know if that helps.

Best regards,
Justyna

 

[mulholm]

[TABLE="width: 223"]
<colgroup><col width="157" style="width: 118pt; mso-width-source: userset; mso-width-alt: 5741;"> <col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <tbody>[TR]
[TD="width: 157, bgcolor: yellow"]Team member 1
[/TD]
[TD="width: 140, bgcolor: yellow"]6:45:00
[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Team member 2
[/TD]
[TD="bgcolor: yellow"]5:24:00[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Team member 3
[/TD]
[TD="bgcolor: yellow"]3:00:00[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Team member 4
[/TD]
[TD="bgcolor: yellow"]4:00:00[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Team member 5
[/TD]
[TD="bgcolor: yellow"]6:32:00[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Team member 6
[/TD]
[TD="bgcolor: yellow"]6:00:00[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Team member 7
[/TD]
[TD="bgcolor: yellow"]6:15:00
[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Team member 8
[/TD]
[TD="bgcolor: yellow"]6:31:00[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Team Total
[/TD]
[TD="bgcolor: yellow"]
[/TD]
[/TR]
</tbody>[/TABLE]

Above is how the table currently appears.

What i want it to perform a sum of team total.
The team members names and team total will always appear in column A and the time signed in will always appear in column B.
The issue i have is that "Team total" changes row number on a daily basis but always stays in column A

 

[mulholm]

The team members names and team total will always appear in column A and the time signed in will always appear in column B.
The issue i have is that "Team total" changes row number on a daily basis but always stays in column A

 

[JustynaMK]

Good, thanks for confirming. If that's the structure then my VBA code should work.
If you run the code then the current position of "Team total" row number should be automatically identified and a SUM function will be created right next to it. Give it a try, then change the position of "Team total", run it again, and let me know if that works for you.

 

[mulholm]

This worked, thanks for your help.

 

[JustynaMK]

cool, thanks for confirming!