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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
337
Office Version
  1. 2019
Platform
  1. Windows
if by loop you mean you need the formula to be placed in range B2 to G6, then try the following

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")
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
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)"

For x = 2 To lr
    For y = 2 To lc
        With wsSummary.Cells(x, y)
            .Formula = sFormula
            wsSummary.Calculate
            .Value2 = .Value2
        End With
    Next y
Next x

hth...
 

MyrenG1

New Member
Joined
Jul 2, 2018
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This is some good bit of code which solves part of what I need. The next part is changing the mentioned elements after each occurrence.

So in Cell B2 you will have the COUNTIFS string shown, but in Cell C2 the COUNTIFS string will need the following changes:-

- $A$2 will need to change to $B$2 and so on ...

Hope this explains it well enough.

Thank you for your help so far.
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
337
Office Version
  1. 2019
Platform
  1. Windows
do you mean this?...
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")
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column

For X = 2 To lr
    For Y = 2 To lc
       sFormula = "COUNTIFS(Sheet1!$D$D, cells(x,y-1), Sheet1!$E:$E, B1)+COUNTIFS(Sheet2!$D$D, cells(x,y-1), Sheet2!$E:$E, B1) +COUNTIFS(Sheet3!$D$D, cells(x,y-1), Sheet3!$E:$E, B1) + COUNTIFS(Sheet4!$D$D, cells(x,y-1), Sheet4!$E:$E, B1) + COUNTIFS(Sheet5!$D$, cells(x,y-1), Sheet5!$E:$E, B1) + COUNTIFS(Sheet6!$D$, cells(x,y-1), Sheet6!$E:$E, B1)"

        With wsSummary.Cells(X, Y)
            .Formula = sFormula
            wsSummary.Calculate
            .Value2 = .Value2
        End With
    Next Y
Next X
 

MyrenG1

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

ADVERTISEMENT

Can it be done with a cell I specify?
 

MyrenG1

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

ADVERTISEMENT

I just tested the code, it doesn't seem to populate the table like the example I posted here.

So the COUNTIFS is looking at 6 different tabs (sheets) with 2 parts of criteria for each sheet.

Criteria 1:-
"B2" = Sheet1
Criteria 2:-
"A2" = Yes

If you look in the COUNTIFS I have there, it repeats 6 times (the number or tabs I have in my spreadsheet).

This in mind, it needs to be calculated across the 6 cells B2:G2 and the only value that will change in each cell with be:

"B1" which will change to "C1", "D1", "E1" etc ... up to G1 (the number of entries in the row).

I'm not sure if this is making any sense, or complicating it.

Appreciate you taking the time to help.
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
337
Office Version
  1. 2019
Platform
  1. Windows
hi,
if , I understood correctly, the criteria you are looking for is to count Yes/No across all your worksheets (from sheet1 to sheet6) and the values placed in B1 to G1 is the sheet reference only, then try the following:
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")
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column

For x = 2 To lr
    For y = 2 To lc
        sname = Cells(1, y)
        cname = Cells(x, 1)
        wsSummary.Cells(x, y) = Application.WorksheetFunction.CountIfs(Sheets(sname).Range("D:D"), cname)
    Next y
Next x
in my opinion there is no need for you to add all countifs and place the formula inside the cells, the values can be computed using vba only.
hth...
 

MyrenG1

New Member
Joined
Jul 2, 2018
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I appreciate the help with this. I can't seem to get this to work with the data I am working with.

Have you managed to have a look at the dummy sheet I provided and run the script on it?

Thanks,
 

Watch MrExcel Video

Forum statistics

Threads
1,130,241
Messages
5,641,041
Members
417,190
Latest member
Sdwd76

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