Loop in macro doesn't work, call statement will - I don't understand

[288enzo]

I am actually going out of my mind with this. It works, then it doesn't.

The bit that is driving me crazy is

Sub calc2()
    Dim modules As Long, lrow As Long
    modules = 6
    lrow = Sheets("sheet0").Range("A1").CurrentRegion.Rows.Count
    For a = 1 To modules
        Range("I2").Offset(, a).Value = _
            "=COUNTIFS(Sheet0!$H$4:$H$" & lrow & ",$F2,Sheet0!$A$4:$A$" & lrow & "," & Range("I1").Offset(, a).Address(external:=True) & ",Sheet0!$D$4:$D$" & lrow & ",""Completed"")" 'count if course is complete
    Next a
End Sub

Has_Has_Not_Taken_Report_04222022.xlsx.xls
	J	K	L	M	N	O
1	619155M5B	619155M6B	619155M7B	619156B	619158B	VCIWB01SSFIA22
2	1	1	1	1	0	1
Data

Cell Formulas
Range	Formula
J2	J2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$J$1,Sheet0!$D$4:$D$6645,"Completed")
K2	K2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$K$1,Sheet0!$D$4:$D$6645,"Completed")
L2	L2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$L$1,Sheet0!$D$4:$D$6645,"Completed")
M2	M2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$M$1,Sheet0!$D$4:$D$6645,"Completed")
N2	N2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$N$1,Sheet0!$D$4:$D$6645,"Completed")
O2	O2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$O$1,Sheet0!$D$4:$D$6645,"Completed")

When I run the above it works perfectly, when I incorporate it into the below, it doesn't.

Sub macrotest()
    Dim modules As Long, lrow As Long, lcol As Long
    
    Sheets.Add.Name = "Data"
    Sheets("Sheet0").Range(Sheets("sheet0").Range("A4"), Sheets("sheet0").Range("A4").End(xlDown)).Copy Range("A1") 'copy list of course codes
        
    Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlNo 'remove duplicate course codes
    
    modules = Range("A1").CurrentRegion.Rows.Count
    
    Range("A1").CurrentRegion.Copy
    Range("J1").PasteSpecial , Transpose:=True 'move course codes across columns
    
    Range("J1").Offset(, modules).Value = "Total"

    lrow = Sheets("sheet0").Range("A1").CurrentRegion.Rows.Count 'get last row of sheet0 to copy data
    
    Range(Sheets("Sheet0").Range("N3"), Sheets("Sheet0").Range("O3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("A1")
    Range(Sheets("Sheet0").Range("Q3"), Sheets("Sheet0").Range("Q3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("C1")
    Range(Sheets("Sheet0").Range("L3"), Sheets("Sheet0").Range("M3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("D1")
    Range(Sheets("Sheet0").Range("H3"), Sheets("Sheet0").Range("H3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("F1")
    Range(Sheets("Sheet0").Range("F3"), Sheets("Sheet0").Range("G3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("G1")
    Range(Sheets("Sheet0").Range("K3"), Sheets("Sheet0").Range("K3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("I1")
    
    Range("A1").CurrentRegion.RemoveDuplicates Columns:=6, Header:=xlNo 'remove duplicates
    
    lrow = Range(Range("A1"), Range("A1").End(xlDown)).Count 'find last row after removing duplicates
    
    For a = 1 To modules
        Range("I2").Offset(, a).Value = _
            "=COUNTIFS(Sheet0!$H$4:$H$" & lrow & ",$F2,Sheet0!$A$4:$A$" & lrow & "," & Range("I1").Offset(, a).Address(external:=True) & ",Sheet0!$D$4:$D$" & lrow & ",""Completed"")" 'count if course is complete
    Next a
End Sub

Has_Has_Not_Taken_Report_04222022.xlsx.xls
	J	K	L	M	N	O
1	619155M5B	619155M6B	619155M7B	619156B	619158B	VCIWB01SSFIA22
2	1	0	0	0	0	0
Data

Cell Formulas
Range	Formula
J2	J2	=COUNTIFS(Sheet0!$H$4:$H$1108,$F2,Sheet0!$A$4:$A$1108,Data!$J$1,Sheet0!$D$4:$D$1108,"Completed")
K2	K2	=COUNTIFS(Sheet0!$H$4:$H$1108,$F2,Sheet0!$A$4:$A$1108,Data!$K$1,Sheet0!$D$4:$D$1108,"Completed")
L2	L2	=COUNTIFS(Sheet0!$H$4:$H$1108,$F2,Sheet0!$A$4:$A$1108,Data!$L$1,Sheet0!$D$4:$D$1108,"Completed")
M2	M2	=COUNTIFS(Sheet0!$H$4:$H$1108,$F2,Sheet0!$A$4:$A$1108,Data!$M$1,Sheet0!$D$4:$D$1108,"Completed")
N2	N2	=COUNTIFS(Sheet0!$H$4:$H$1108,$F2,Sheet0!$A$4:$A$1108,Data!$N$1,Sheet0!$D$4:$D$1108,"Completed")
O2	O2	=COUNTIFS(Sheet0!$H$4:$H$1108,$F2,Sheet0!$A$4:$A$1108,Data!$O$1,Sheet0!$D$4:$D$1108,"Completed")

Now, this is where it gets interesting. If I add a call statement to my loop outside of the macro, it works. It's the same darn loop. I am beside myself.

Sub macrotest()
    Dim modules As Long, lrow As Long, lcol As Long
    
    Sheets.Add.Name = "Data"
    Sheets("Sheet0").Range(Sheets("sheet0").Range("A4"), Sheets("sheet0").Range("A4").End(xlDown)).Copy Range("A1") 'copy list of course codes
        
    Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlNo 'remove duplicate course codes
    
    modules = Range("A1").CurrentRegion.Rows.Count
    
    Range("A1").CurrentRegion.Copy
    Range("J1").PasteSpecial , Transpose:=True 'move course codes across columns
    
    Range("J1").Offset(, modules).Value = "Total"

    lrow = Sheets("sheet0").Range("A1").CurrentRegion.Rows.Count 'get last row of sheet0 to copy data
    
    Range(Sheets("Sheet0").Range("N3"), Sheets("Sheet0").Range("O3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("A1")
    Range(Sheets("Sheet0").Range("Q3"), Sheets("Sheet0").Range("Q3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("C1")
    Range(Sheets("Sheet0").Range("L3"), Sheets("Sheet0").Range("M3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("D1")
    Range(Sheets("Sheet0").Range("H3"), Sheets("Sheet0").Range("H3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("F1")
    Range(Sheets("Sheet0").Range("F3"), Sheets("Sheet0").Range("G3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("G1")
    Range(Sheets("Sheet0").Range("K3"), Sheets("Sheet0").Range("K3").Offset(lrow - 3).End(xlDown)).Copy Destination:=Range("I1")
    
    Range("A1").CurrentRegion.RemoveDuplicates Columns:=6, Header:=xlNo 'remove duplicates
    
    lrow = Range(Range("A1"), Range("A1").End(xlDown)).Count 'find last row after removing duplicates
    
    Call calc2
End Sub

Has_Has_Not_Taken_Report_04222022.xlsx.xls
	J	K	L	M	N	O
1	619155M5B	619155M6B	619155M7B	619156B	619158B	VCIWB01SSFIA22
2	1	1	1	1	0	1
Data

Cell Formulas
Range	Formula
J2	J2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$J$1,Sheet0!$D$4:$D$6645,"Completed")
K2	K2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$K$1,Sheet0!$D$4:$D$6645,"Completed")
L2	L2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$L$1,Sheet0!$D$4:$D$6645,"Completed")
M2	M2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$M$1,Sheet0!$D$4:$D$6645,"Completed")
N2	N2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$N$1,Sheet0!$D$4:$D$6645,"Completed")
O2	O2	=COUNTIFS(Sheet0!$H$4:$H$6645,$F2,Sheet0!$A$4:$A$6645,Data!$O$1,Sheet0!$D$4:$D$6645,"Completed")

 

[Zot]

When you incorporate, I can see that you have 2 statements on lrow.

1) lrow = Sheets("sheet0").Range("A1").CurrentRegion.Rows.Count 'get last row of sheet0 to copy data
2) lrow = Range(Range("A1"), Range("A1").End(xlDown)).Count 'find last row after removing duplicates

As you can see in your formula, the lrow is different from the one that worked and the one that did not work. The lrow refers to 2 different sheets as I see it (unless I misunderstood )

 

[288enzo]

When you incorporate, I can see that you have 2 statements on lrow.

1) lrow = Sheets("sheet0").Range("A1").CurrentRegion.Rows.Count 'get last row of sheet0 to copy data
2) lrow = Range(Range("A1"), Range("A1").End(xlDown)).Count 'find last row after removing duplicates

As you can see in your formula, the lrow is different from the one that worked and the one that did not work. The lrow refers to 2 different sheets as I see it (unless I misunderstood )

Life savor!

I can't thank you enough. I removed the second lrow and all is good. Not sure why I had that in there in the first place.

I can sleep tonight.