Hello,

This is my first post and I've searched everywhere for this. However, as I'm a VBA beginner I am struggling to simplify this VBA formula.

I have 2 sheets.
1) Sheet 1 is the source data sheet with survey responses for each question in column format (eg A-Z).
2) Sheet 2 is an array table which counts the survey resources where multiple criterions are matched (ie using COUNTIF formula).

The formula is generally the same. Two of the criterions are in row 1 of sheet 2. Only the columns change and they progress consecutively (eg from A to B) for each row of the array table. Please refer to the columns in bold font. As there are 75 survey questions, I would prefer not to copy and paste the recorded VBA formula.

Below is an example of a formula which I would like to loop and repeat.

Sheet2.Range("C2").FormulaR1C1 = _
"=COUNTIFS('Sheet1'!C81,""*""&'RPT - Rank'!R1C2&""*"",'Sheet1'!C1,'RPT - Rank'!R1C)"
Sheet2.Range("C2").AutoFill Destination:=Range("C2:G2"), Type:=xlFillDefault

Sheet2.Range("C3").FormulaR1C1 = _
"=COUNTIFS('Sheet1'!C81,""*""&'RPT - Rank'!R1C2&""*"",'Sheet1'!C2,'RPT - Rank'!R1C)"
Sheet2.Range("C3").AutoFill Destination:=Range("C3:G3"), Type:=xlFillDefault


Can anyone help? I need this next week.