VBA add a criteria to a Sum function

[weefatb0b]

Hello, I have a macro, below which works great but I have been asked to add another criteria to it, this one does it for each blank cell in column C, what I have been asked for is to only sum column C if the value in column B is in Column I, I have tried various but cant work out how to do this, any ideas would be brilliant? Thanks in advance

Sub TOTALCOLUMNC()
    Dim ws As Worksheet
    Set ws = Worksheets("Position") 'define worksheet here

    Dim FirstCell As Range
    Set FirstCell = ws.Range("C2")

    Dim VeryLastCell As Range 'get very last cell as stop criteria
    Set VeryLastCell = ws.Cells(ws.Rows.Count, "I").End(xlUp)

    Do
        Dim LastCell As Range
        If FirstCell.Offset(1) = vbNullString Then 'test if there is only one cell to sum
            Set LastCell = FirstCell
        Else
            Set LastCell = FirstCell.End(xlDown)
        End If

        With LastCell.Offset(1, 0) 'this is the cell we want to write the sum
            .Value = Application.WorksheetFunction.Sum(ws.Range(FirstCell, LastCell))
            .Interior.Color = RGB(192, 192, 192)
        End With

        Set FirstCell = LastCell.Offset(2, 0)

    Loop While FirstCell.Row < VeryLastCell.Row
 
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[weefatb0b]

Apologies Peter and thanks

 

[Akuini]

Could you please provide around 10 rows of sample data along with the expected results?

 

[weefatb0b]

Hi, afraid I can't as I cannot get the uploader to work, I have attached an image, hopefully this will help

 

[Peter_SSs]

I cannot get the uploader to work,

Do you mean XL2BB?
If so, what actually goes wrong that you cannot use it?

 

[weefatb0b]

I have tried a few times to install it to upload and I cant get it installed or if i do it doesnt upload the workbook, I think its because i am on a work computer and am restricted

 

[Akuini]

Can you just copy your table and paste it here? You can use mock data, around 10 rows should be enough to explain what you're trying to do.

 

[weefatb0b]

Position Title

Establishment Count

Occupied

Vacant

Vacant %

Unit/Dept

Serv. Area

HOS

Position 1

0.93

0.00

0.93

Unit 1

Service Area 1

Head of Service 1

Position 2

0.00

0.96

-0.96

Unit 1

Service Area 1

Head of Service 1

Position 3

2.10

0.77

1.33

Unit 1

Service Area 1

Head of Service 1

Position 4

0.00

1.00

-1.00

Unit 1

Service Area 1

Head of Service 1

Position 5

0.00

0.05

-0.05

Unit 1

Service Area 1

Head of Service 1

Position 6

1.62

0.62

1.00

Unit 1

Service Area 1

Head of Service 1

Position 7

0.00

1.00

-1.00

Unit 1

Service Area 1

Head of Service 1

Position 8

1.00

1.00

0.00

Unit 1

Service Area 1

Head of Service 1

Position 9

4.70

0.00

4.70

Unit 1

Service Area 1

Head of Service 1

Position 10

0.00

0.41

-0.41

Unit 1

Service Area 1

Head of Service 1

Position 11

0.00

2.41

-2.41

Unit 1

Service Area 1

Head of Service 1

Position 12

0.00

0.41

-0.41

Unit 1

Service Area 1

Head of Service 1

Position 13

3.27

0.87

2.40

Unit 1

Service Area 1

Head of Service 1

Position 14

0.00

0.87

-0.87

Unit 1

Service Area 1

Head of Service 1

Position 15

0.00

0.87

-0.87

Unit 1

Service Area 1

Head of Service 1

Position 16

0.00

0.87

-0.87

Unit 1

Service Area 1

Head of Service 1

Position 17

1.03

0.00

1.03

Unit 1

Service Area 1

Head of Service 1

Position 18

0.00

0.41

-0.41

Unit 1

Service Area 1

Head of Service 1

Position 19

0.62

0.00

0.62

Unit 1

Service Area 1

Head of Service 1

Unit 1

Service Area 1

Head of Service 1

Position 1

1.04

1.14

-0.10

Unit 2

Service Area 1

Head of Service 1

Position 2

0.08

0.31

-0.23

Unit 2

Service Area 1

Head of Service 1

Position 3

3.00

1.00

2.00

Unit 2

Service Area 1

Head of Service 1

Position 4

0.00

1.00

-1.00

Unit 2

Service Area 1

Head of Service 1

Position 5

0.00

1.00

-1.00

Unit 2

Service Area 1

Head of Service 1

Position 6

6.28

1.29

4.99

Unit 2

Service Area 1

Head of Service 1

Position 7

0.00

0.00

0.00

Unit 2

Service Area 1

Head of Service 1

Position 8

0.00

1.81

-1.81

Unit 2

Service Area 1

Head of Service 1

Position 9

0.00

0.51

-0.51

Unit 2

Service Area 1

Head of Service 1

Position 10

1.81

0.87

0.94

Unit 2

Service Area 1

Head of Service 1

Position 11

0.00

0.87

-0.87

Unit 2

Service Area 1

Head of Service 1

Position 12

0.33

0.49

-0.16

Unit 2

Service Area 1

Head of Service 1

Position 13

0.77

0.00

0.77

Unit 2

Service Area 1

Head of Service 1

Position 14

0.77

0.38

0.39

Unit 2

Service Area 1

Head of Service 1

Unit 2

Service Area 1

Head of Service 1

Head of Service 1

Macro required for Step 1 to total all Column B, C & D in to row 52 before doing Step 2 where value in yellow cell A52 is in Column H and then continue all the way to last cell populated in column A

Macro to input figures are Step 2

 

[weefatb0b]

I am looking to total Establishment, Occupied and Vacant columns where the value Head of Service 1 is in the last column I have a macro, stage 2 which will total for each Unit