VBA add a criteria to a Sum function

weefatb0b

New Member
Joined
Nov 17, 2022
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
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


VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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. 😊
 
Upvote 0
Could you please provide around 10 rows of sample data along with the expected results?
 
Upvote 0
Hi, afraid I can't as I cannot get the uploader to work, I have attached an image, hopefully this will help
 

Attachments

  • Macro Test.JPG
    Macro Test.JPG
    196.5 KB · Views: 9
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Position TitleEstablishment CountOccupiedVacantVacant %Unit/DeptServ. AreaHOS
Position 10.930.000.93Unit 1Service Area 1Head of Service 1
Position 20.000.96-0.96Unit 1Service Area 1Head of Service 1
Position 32.100.771.33Unit 1Service Area 1Head of Service 1
Position 40.001.00-1.00Unit 1Service Area 1Head of Service 1
Position 50.000.05-0.05Unit 1Service Area 1Head of Service 1
Position 61.620.621.00Unit 1Service Area 1Head of Service 1
Position 70.001.00-1.00Unit 1Service Area 1Head of Service 1
Position 81.001.000.00Unit 1Service Area 1Head of Service 1
Position 94.700.004.70Unit 1Service Area 1Head of Service 1
Position 100.000.41-0.41Unit 1Service Area 1Head of Service 1
Position 110.002.41-2.41Unit 1Service Area 1Head of Service 1
Position 120.000.41-0.41Unit 1Service Area 1Head of Service 1
Position 133.270.872.40Unit 1Service Area 1Head of Service 1
Position 140.000.87-0.87Unit 1Service Area 1Head of Service 1
Position 150.000.87-0.87Unit 1Service Area 1Head of Service 1
Position 160.000.87-0.87Unit 1Service Area 1Head of Service 1
Position 171.030.001.03Unit 1Service Area 1Head of Service 1
Position 180.000.41-0.41Unit 1Service Area 1Head of Service 1
Position 190.620.000.62Unit 1Service Area 1Head of Service 1
Unit 1Service Area 1Head of Service 1
Position 11.041.14-0.10Unit 2Service Area 1Head of Service 1
Position 20.080.31-0.23Unit 2Service Area 1Head of Service 1
Position 33.001.002.00Unit 2Service Area 1Head of Service 1
Position 40.001.00-1.00Unit 2Service Area 1Head of Service 1
Position 50.001.00-1.00Unit 2Service Area 1Head of Service 1
Position 66.281.294.99Unit 2Service Area 1Head of Service 1
Position 70.000.000.00Unit 2Service Area 1Head of Service 1
Position 80.001.81-1.81Unit 2Service Area 1Head of Service 1
Position 90.000.51-0.51Unit 2Service Area 1Head of Service 1
Position 101.810.870.94Unit 2Service Area 1Head of Service 1
Position 110.000.87-0.87Unit 2Service Area 1Head of Service 1
Position 120.330.49-0.16Unit 2Service Area 1Head of Service 1
Position 130.770.000.77Unit 2Service Area 1Head of Service 1
Position 140.770.380.39Unit 2Service Area 1Head of Service 1
Unit 2Service Area 1Head 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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top