VBA Survey Issues, Mismatch 13 Error Code

[Kreese11]

Hello! I am super new to creating VBAs in Excel and in fact this is my first attempt. I am trying to create a survey and what happens is when someone leaves a spot blank when they submit the survey then a Message Box will populate advising that need to go back to that question and fill in the box. However, I keep getting a runtime error 13 code advising a mismatch type. I research and found that it means that there is an integer issue, but I am not make this box an integer. Basically, if someone leaves thier name blank then they need to go back and enter a name.

Also, if you guys see any additional potential errors please let me know

I am using excel 2007

Here is the code that I created. The text in bold red is where I'm having the issue:

Private Sub CommandButton1_Click()
date_answer = Sheets("Survey").Range("C4")
name_answer = Sheets("Survey").Range("C6:D6")
EmailID_answer = Sheets("Survey").Range("C8:D8")
department_answer = Sheets("Survey").Range("C10:D10")
DeptNumber_answer = Sheets("Survey").Range("F10")
RequestType = Sheets("Survey").Range("C12:D12")
TitleofCommunication_answer = Sheets("Survey").Range("C14:I14")
DetailofRequest_answer = Sheets("Survey").Range("C16:I24")
contact_answer = Sheets("Survey").Range("C26:I26")
BCC_answer = Sheets("Survey").Range("C28:I28")
approvers_answer = Sheets("Survey").Range("C30:I30")
TranslationNeeded_answer = Sheets("Survey").Range("C32")
startdate_answer = Sheets("Survey").Range("C34")
EndDate_answer = Sheets("Survey").Range("C36")
IncrementalSales_answer = Sheets("Survey").Range("C38:D38")
CostSavings_answer = Sheets("Survey").Range("C40:D40")
numberofhours_answer = Sheets("Survey").Range("C43:D43")
Cost_answer = Sheets("Survey").Range("C47:D47")
TotalCost_answer = Sheets("Survey").Range("C49:D49")
ROI_answer = Sheets("Survey").Range("C51:D51")
ExecutionCostNumberofHours_answer = Sheets("Survey").Range("F43:G43")
executioncostnumberofstores_answer = Sheets("Survey").Range("F45:G45")
ExecutionCostNumberofCost_answer = Sheets("Survey").Range("F47:G47")


If date_answer = "" Then
MsgBox "Fill in Date"
Exit Sub
End If


If name_answer = "" Then
MsgBox "Fill in Name"
Exit Sub
End If


If EmailID_answer = "" Then
MsgBox "Fill in Email ID"
Exit Sub
End If


If department_answer = "" Then
MsgBox "Fill in Department"
Exit Sub
End If


If RequestType_answer = "" Then
MsgBox "Fill in Request Type"
Exit Sub
End If


If TitleofCommunication = "" Then
MsgBox "Fill in Title of Communication"
Exit Sub
End If


If DetailsofRequest_answer = "" Then
MsgBox "Fill in Request Details"
Exit Sub
End If


If contact_answer = "" Then
MsgBox "Fill in Contact Name"
Exit Sub
End If


If approvers_answer = "" Then
MsgBox "Fill in Approver(s) Name"
Exit Sub
End If


If TranslationNeed_answer = "" Then
MsgBox "Fill in Translation"
Exit Sub
End If


If startdate_answer = "" Then
MsgBox "Fill in Requested Start Date"
Exit Sub
End If


If EndDate_answer = "" Then
MsgBox "Fill in Requested End Date"
Exit Sub
End If


If IncrementalSales_answer = "" Then
MsgBox "Fill in Incremental Sales"
Exit Sub
End If


If CostSavings_answer = "" Then
MsgBox "Fill in Cost Savings"
Exit Sub
End If


If numberofhours_answer = "" Then
MsgBox "Fill in Number of Hours Needed for Request"
Exit Sub
End If


If numberofstores_answer = "" Then
MsgBox "Fill in Number of Stores Included"
Exit Sub
End If


If ExecutionCostNumberofHours_answer = "" Then
MsgBox "Fill in Hours Need for the Execution"
Exit Sub
End If


If executioncostnumberofstores_answer = "" Then
MsgBox "Fill in Number of Stores Included"
Exit Sub
End If




row_number = 1
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("Results").Range("A" & row_number)
Loop Until item_in_review = ""


last_transaction_id = Sheets("Results").Range("A" & (row_number - 1))


Dim next_transaction_id As Integer
next_transaction_id = last_transaction_id + 1


Sheets("Answers").Range("A" & (row_number)) = next_transaction_id
Sheets("Answers").Range("B" & (row_number)) = Date
Sheets("Answers").Range("C" & (row_number)) = Name
Sheets("Answers").Range("D" & (row_number)) = EmailID
Sheets("Answers").Range("E" & (row_number)) = Department
Sheets("Answers").Range("F" & (row_number)) = DeptNumber
Sheets("Answers").Range("G" & (row_number)) = RequestType
Sheets("Answers").Range("H" & (row_number)) = TitleofCommunication
Sheets("Answers").Range("I" & (row_number)) = DetailsofRequest
Sheets("Answers").Range("J" & (row_number)) = Contact
Sheets("Answers").Range("K" & (row_number)) = BCC
Sheets("Answers").Range("L" & (row_number)) = Approvers
Sheets("Answers").Range("M" & (row_number)) = TranslationNeeded
Sheets("Answers").Range("N" & (row_number)) = StartDate
Sheets("Answers").Range("O" & (row_number)) = EndDate
Sheets("Answers").Range("P" & (row_number)) = IncrementalSales
Sheets("Answers").Range("Q" & (row_number)) = CostSavings
Sheets("Answers").Range("R" & (row_number)) = NumberofHours
Sheets("Answers").Range("S" & (row_number)) = NumberofStores
Sheets("Answers").Range("T" & (row_number)) = Cost
Sheets("Answers").Range("U" & (row_number)) = TotalCost
Sheets("Answers").Range("V" & (row_number)) = ROI
Sheets("Answers").Range("W" & (row_number)) = ExecutionCostNumberofHours
Sheets("Answers").Range("X" & (row_number)) = ExecutionCostNumberofStores
Sheets("Answers").Range("X" & (row_number)) = ExecutionCostNumberCost
Sheets("Answers").Range("X" & (row_number)) = ExecutionCostNumberCost


End Sub

 

[John_w]

Welcome to MrExcel.

This code isolates the issue:

Private Sub Test()
    Dim name_answer
    
    name_answer = Sheets("Survey").Range("C6:D6")
    
    If name_answer(1, 1) = "" Or name_answer(1, 2) = "" Then
        MsgBox "Fill in Name"
        Exit Sub
    End If

End Sub

"C6:D6" is 2 cells and name_answer is therefore a 2-dimensional array (row index, column index). name_answer(1, 1) is the C6 value and name_answer(1, 2) is the D6 value. As shown above you must check the individual array elements.

The same solution applies where a piece of the survey data is contained in more than one row or column.

PS - please put code inside CODE tags, like this:

[CODE]
VBA code here
[/CODE]