Structured Referencing and If Functions

[rkorinko]

Howdy ya'll!

I am writing a program that utilizes a table on sheet 1, to do countif formulas and percentages on worksheet two in the same workbook. I have this base part set up, but am having trouble with error handling.

Background: I am using polling data that changes constantly-the number of questions in a poll and the number of responses. The idea here is to automate some of our reporting. I am using Excel 2010.

I am creating a macro that will run no matter how many questions/responses may exist in a given table. I have an idea of the highest numbers possible which is what I plan on writing in. No more than 15 responses and no more than 75 questions.

My initial thought was to set up an If-Then statement for each possible question. This may be time-consuming, and not the prettiest bit of code, but it ought to in theory work. Something like If X exists then do A else do B and exit the macro. And have a separate if statement for each possible question in a survey.

Below is the code that I am trying to work from, but I keep getting the error "Object doesn't support this property or method."--the bolded code. Can I not use If statements with Tables? Or am I missing something?

If not, the other option I was beginning to look into is are the INDEX-MATCH functions, but I don't want the sub to print anything-I just want it to know whether or not to do the counts for each question that may exist.

Sub CreatingCounts()

'This creates the response counts for each potential question and response


[B]If Sheets("Sheet1").ObjectList("Table1[Q1]") = True Then[/B]


'Index(Table1,MATCH("Q1",Sheets("Sheet1").Table1[#Headers],0))" Go To
    
    Sheets("Sheet2").[A2] = "=COUNTIF(Table1[Q1],1)"
    Sheets("Sheet2").[A3] = "=COUNTIF(Table1[Q1],2)"
    Sheets("Sheet2").[A4] = "=COUNTIF(Table1[Q1],3)"
    Sheets("Sheet2").[A5] = "=COUNTIF(Table1[Q1],4)"
    Sheets("Sheet2").[A6] = "=COUNTIF(Table1[Q1],5)"
    Sheets("Sheet2").[A7] = "=COUNTIF(Table1[Q1],6)"
    Sheets("Sheet2").[A8] = "=COUNTIF(Table1[Q1],7)"
    Sheets("Sheet2").[A9] = "=COUNTIF(Table1[Q1],8)"
    Sheets("Sheet2").[A10] = "=COUNTIF(Table1[Q1],9)"
    Sheets("Sheet2").[A11] = "=COUNTIF(Table1[Q1],10)"
    Sheets("Sheet2").[A12] = "=COUNTIF(Table1[Q1],11)"
    Sheets("Sheet2").[A13] = "=COUNTIF(Table1[Q1],12)"
    Sheets("Sheet2").[A14] = "=COUNTIF(Table1[Q1],13)"
    Sheets("Sheet2").[A15] = "=COUNTIF(Table1[Q1],14)"
    Sheets("Sheet2").[A16] = "=COUNTIF(Table1[Q1],15)"
Else
    MsgBox ("The data has been counted.")
    
End If
    
' Doing a test for failure to see how things come into play


' Sheets("Sheet2").[C2] = "=Countif(Table1[Q29], 1)"


    
'Creating a total function for the number of responses


Sheets("Sheet2").[A17] = "=SUM(A2:A16)"


'Using counts and totals to develop the frequency percentages of responses


Sheets("Sheet2").[B2] = "=A2/$A$17"


'Turn some columns into percentages.  Must be in A:A format and seperated by commas


Sheets("Sheet2").Range("B:B,D:D").NumberFormat = "0.00%"


'If error occurs, do this--I know this isn't in the correct location, but I needed a place holder for it or something like it
On Error GoTo Errhandler
Exit Sub
Errhandler:
    MsgBox "The macro has finished."


End Sub

Just to reiterate, my biggest concern is getting the COUNTIF code to run only if the explicit header is in the Table and to gracefully stop if it is not. The percentages and formatting cells are secondary at this juncture.

Thanks!!!