VBA countif formula for increment columns

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.

Maybe try this?

Please Login or Register  to view this content.

Hi, the FillRight works. For the FillDown, it is close but I need the following column to increment for each row after the first row:

Eg if this script, 'RPT - Rank'!R1C2, is column B then for the next row, it should reference column C, and for the next column, column D etc.

Any help would be appreciated!

Oops, the above post is not right.

The FillRight works. For the Filldown, this is actually what I need:

Eg if this script, 'Sheet1'!C1, is column B then for the next row, it should reference column C, and for the next column, column D etc.

Sorry for the confusion. Any help would be appreciated!

try changing to

Please Login or Register  to view this content.

It doesn't work! When I changed it, it became column D instead of A with no $ and copied column D down all the below rows. What I would like is for the following rows, the column will change from column D to E, F, G etc. Is that clearer?

Try this. I don't know how to do this without a VBA loop.

Please Login or Register  to view this content.

This looks promising but I'm unsure how to use this. I've created and attached an example, would you be able to show me how this VBA loop work?

I'm having problems uploading an example but the idea as below:

Sheet 2
# COL81_DG 1 2 3 4
1 text 0 0 1 0
2 text 1 1 0 0
3 text 2 0 0 0
4 text 1 0 1 0
5 text 0 0 2 0


Sheet 1
COL1 COL2 COL3 COL4 COL5 etc COL80 COL81
4 4 2 3 3 ab COL81_DE
4 3 2 2 2 ab COL81_DE
4 4 4 4 4 ab COL81_DF
1 3 2 4 4 ab COL81_DG
4 1 1 3 3 ab COL81_DE
2 2 2 4 4 ab COL81_DF
1 3 1 2 4 ab COL81_DE
4 3 1 2 3 ab COL81_DE
4 2 2 2 4 ab COL81_DG