Can this be done in a Loop?

MyrenG1

New Member
Joined
Jul 2, 2018
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

I've been through this in a painfully repetitive manual way. I was wondering if there is a way of placing the below in a Loop and only changing a few things inside the "COUNTIFS" statement and wsSummary.Range.

I haven't done any loops before, I am still fairly new when it comes to VBA scripting. I just know how to adapt coding to help me with what I try to achieve.

Any help with the below would be appreciated. Thank you for your time in Advance.

The elements inside the COUNTIFS that change are:-

- "Sheet1"
- "$A$23"

Also:-

- "With wsSummary.Range("B1")"

It will need to loop approx 6 times to complete population of the Table. If this can work, I should be able to adapt it to my other tables and forumla I have.

VBA Code:
' Identify Sheet as worksheet'

Dim wsSummary As Worksheet

' Identify Formula as a String'

Dim sFormula As String

' Calculate Yes or No Totals'

' Total Yes for Sheet1'

    Set wsSummary = Sheets("Summary")
    sFormula = "COUNTIFS(Sheet1!$D$D, $A$2, Sheet1!$E:$E, B1)+COUNTIFS(Sheet2!$D$D, $A$2, Sheet2!$E:$E, B1)+COUNTIFS(Sheet3!$D$D, $A$2, Sheet3!$E:$E, B1)+COUNTIFS(Sheet4!$D$D, $A$2, Sheet4!$E:$E, B1)+COUNTIFS(Sheet5!$D$, $A$2, Sheet5!$E:$E, B1)+COUNTIFS(Sheet6!$D$, $A$2, Sheet6!$E:$E, B1)"
    
With wsSummary.Range("B2")
    .Formula = sFormula
    wsSummary.Calculate
    .Value2 = .Value2
End With

Example Table below of the results I am looking for:-

ABCDEFG
1Sheet1Sheet2Sheet3Sheet4Sheet5Sheet6
2Yes123456
3No654321
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
337
Office Version
  1. 2019
Platform
  1. Windows
yes i did and the result was what you posted in your first post. are you getting any errors with the code? ehat is the result??
 

Attachments

  • IMG_20201216_204707_691.jpg
    IMG_20201216_204707_691.jpg
    28.7 KB · Views: 2

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

MyrenG1

New Member
Joined
Jul 2, 2018
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have an extra column for "Total" in row H which is where it Stops and the Code Error's. I assume because it can't find a sheet named "Total".
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
337
Office Version
  1. 2019
Platform
  1. Windows
this is most probably the case. cant say for sure since i havent seen your worksheet. what is the error you are recieveing? i would suggest sharing part of yout worksheet on free sites dropbox or google drive, to have alook at.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,132
Messages
5,640,306
Members
417,136
Latest member
reeton3

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
Top