Thread: Coding CountIf Statements

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:

Code:

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.

I've been playing with this for the last day and have some ideas. I didn't see any responses, so I figured I'd post what I found in case anyone else runs into the same issue.

When I recorded the macro to get the formula base for the VBA, the recorded gave me the format you see in the original posting. I don't really understand the [r] and [c] bit, however it appears to be taking the current row. So... to get it to use just the variable I was able to modify the code and have it substract the current row number. Below is an excerpt from the code I ended up using. Its not the cleanest way to do this, but it worked. Hope this helps someone else.

Code:

    Sheets("Test Responses").Select
    Range("A1").Select
    If CurrentQuestion = ActiveCell.Value Then
       
        Range("A2").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(TestTemp!R[" & QuestionRangeStart - 2 & "]C[6]:R[" & QuestionRangeEnd - 2 & "]C[6],""="" & RC[1])"
        Range("A3").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(TestTemp!R[" & QuestionRangeStart - 3 & "]C[6]:R[" & QuestionRangeEnd - 3 & "]C[6],""="" & RC[1])"
        Range("A4").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(TestTemp!R[" & QuestionRangeStart - 4 & "]C[6]:R[" & QuestionRangeEnd - 4 & "]C[6],""="" & RC[1])"
        Range("A5").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(TestTemp!R[" & QuestionRangeStart - 5 & "]C[6]:R[" & QuestionRangeEnd - 5 & "]C[6],""="" & RC[1])"
        Range("A6").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(TestTemp!R[" & QuestionRangeStart - 6 & "]C[6]:R[" & QuestionRangeEnd - 6 & "]C[6],""="" & RC[1])"
        Range("A7").Select
            ActiveCell.FormulaR1C1 = _
        "=COUNTIF(TestTemp!R[" & QuestionRangeStart - 7 & "]C[6]:R[" & QuestionRangeEnd - 7 & "]C[6],""="" & RC[1])"

The R and the C come from the R1C1 Reference Style. If you go into Tools > Options > General, the R1C1 reference style box is probably checked. This is simply a different way to reference a cell.

If the box is checked, you might want to uncheck it and rerecord your macro. It might make things a little easier to understand.

Jeff