Can this be done in a Loop?

[MyrenG1]

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.

' 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:-

	A	B	C	D	E	F	G
1		Sheet1	Sheet2	Sheet3	Sheet4	Sheet5	Sheet6
2	Yes	1	2	3	4	5	6
3	No	6	5	4	3	2	1

 

[fadee2]

if by loop you mean you need the formula to be placed in range B2 to G6, then try the following

' 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]

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]

do you mean this?...

' 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]

Can it be done with a cell I specify?

 

[fadee2]

Can it be done with a cell I specify?

what do u mean??

 

[MyrenG1]

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.

 

[MyrenG1]

I've quickly thrown together a dummy sheet to help.

Dummy Data Sheet

let me know if you have trouble accessing.

 

[fadee2]

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:

' 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]

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,