Reference offset cell during loop

[288enzo]

Hello,

I have this bit of code -

Dim email As Variant
Set email = Sheets("Sheet0").Range("H4:H" & Sheets("Sheet0").Range("A" & Rows.Count).End(xlUp).Row)

Dim status As Variant
Set status = Sheets("Sheet0").Range("D4:D" & Sheets("Sheet0").Range("A" & Rows.Count).End(xlUp).Row)

Dim course1 As Variant
Set course1 = Sheets("Sheet0").Range("A4:A" & Sheets("Sheet0").Range("A" & Rows.Count).End(xlUp).Row)
    
lr2 = 5
For x = 0 To lr2

'Range("I2").Offset(0, x) = Range("I1").Offset(0, x)
'Range("I2").Offset(0, x) = "=countifs(" & email.Address(external:=True) & ",G2," & course1.Address(external:=True) & ",Range("I1").Offset(0, x)," & status.Address(external:=True) & ",""Completed"")"

Next x

This is the result I'm looking for using the loop above. I can't figure out how to work Range("I1").Offset(0, x) into my looped formula.

Has_Has_Not_Taken_Report_04132022.xlsx-1.xls
	I	J	K	L	M	N
1	619156B	619155M5B	619155M6B	619155M7B	619156B	VCIWB01SSFIA22
2	1	1	0	0	1	1
Report

Cell Formulas
Range	Formula
I2:N2	I2	=COUNTIFS(Sheet0!$H$4:$H$898,$G$2,Sheet0!$A$4:$A$898,I1,Sheet0!$D$4:$D$898,"Completed")

 

[288enzo]

I think I figured it out

Dim email As Variant
Set email = Sheets("Sheet0").Range("H4:H" & Sheets("Sheet0").Range("A" & Rows.Count).End(xlUp).Row)

Dim status As Variant
Set status = Sheets("Sheet0").Range("D4:D" & Sheets("Sheet0").Range("A" & Rows.Count).End(xlUp).Row)

Dim course1 As Variant
Set course1 = Sheets("Sheet0").Range("A4:A" & Sheets("Sheet0").Range("A" & Rows.Count).End(xlUp).Row)
    
Dim course As Variant

lr2 = 5
For x = 0 To lr2

Set course = Range("I1").Offset(0, x)
Range("I2").Offset(0, x) = "=countifs(" & email.Address(external:=True) & ",G2," & course1.Address(external:=True) & "," & course.Address(external:=True) & "," & status.Address(external:=True) & ",""Completed"")"

Next x