I need help getting a CountIf formula to work correctly in VBA. I am working with marketing data which shows hundreds of responses people gave to multiple choice questions. In each row I have the respondants name, the question asked, and the answer given. I am building a summary sheet that shows each question asked, the possible answers, and the number of times each response was given for each question. The end result I need is a formula that gives the number times each answer was given per question.

I have a subroute which is deternines the QuestionRangeStart and the QuestionRangeEnd for the current question. At that point I try to enter the CountIf formula using these variables. In the end the formula should read as follows:

=COUNTIF(ControlTemp!G11:G96,"=" & A2)
(QuestionRangeStart = 11, Question Range End = 96, Lookup Value = Cell A2.

Because I am new to VBA I tried to create this formula by recording a macro and substituting in the variable names. The current code reads as follows:


Sub InsertControlResponseCount(QuestionRangeStart, QuestionRangeEnd, CurrentQuestion)

    Sheets("Control Responses").Select
    Range("A1").Select
    If CurrentQuestion = ActiveCell.Value Then
       
        Range("A2").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(ControlTemp!R[" & QuestionRangeStart & "]C[6]:R[" & QuestionRangeEnd & "]C[6],""="" & RC[1])"
        Range("A3").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(ControlTemp!R[" & QuestionRangeStart & "]C[6]:R[" & QuestionRangeEnd & "]C[6],""="" & RC[1])"
        Range("A4").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(ControlTemp!R[" & QuestionRangeStart & "]C[6]:R[" & QuestionRangeEnd & "]C[6],""="" & RC[1])"
        Range("A5").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(ControlTemp!R[" & QuestionRangeStart & "]C[6]:R[" & QuestionRangeEnd & "]C[6],""="" & RC[1])"
        Range("A6").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(ControlTemp!R[" & QuestionRangeStart & "]C[6]:R[" & QuestionRangeEnd & "]C[6],""="" & RC[1])"
      
    End If
The problem I run into is that when I enter those variables into the CountIf formula it seems to increment the value each time instead of sticking to the variable. The range should be exactly the same for each response line. Any help would be greatly appreciated.