Excel VBA: Run-time error '13': Type Mismatch

[mondaymorning]

Hi there

Just wondering if anyone could help with this query. I have hit a wall with VBA.

I have inherited an Excel workbook from former colleague.
The workbook is sent out to an organisation who then fill it out and the idea is that when they press Update Pivot Data - all the charts are updated.

I have done logical steps of checking the raw data row by row.
I believe the code for the Workbook does work
But the error may lie in the format of dates?

I keep on getting an error message:
Run-time error: '13'
Type mismatch

Sub Update_Data()
' Update Pivot Data

Dim sheet As Worksheet
Application.ScreenUpdating = False
Set sheet = Sheets("Mirror")

' Participants start from a3
Dim currentRow As Integer
Dim offset As Integer
Dim currentIndex As Integer
Dim row As Object

' We need seperate states for answer and questions
Dim questionHeader As Object
Dim answerHeader As Object

offset = 2
currentRow = 1

currentIndex = 3
Set questionHeader = sheet.Range("I1", sheet.Range("I1").End(xlToRight))
Set answerHeader = sheet.Range("I2", sheet.Range("I2").End(xlToRight))




Dim counter As Integer, questionCounter As Integer, answerCounter As Integer
Dim pivotSheet As Worksheet
counter = 2
questionCounter = 1
Set pivotSheet = Sheets("Pivot_Data")

Dim participants As Object

Set participants = sheet.Range("B3", sheet.Range("B3").End(xlDown))

For Each participant In participants

Set row = sheet.Range("A" & currentIndex, sheet.Range("A" & currentIndex).End(xlToRight))

For Each Cell In answerHeader
Dim currentAnswer As String, currentQuestion As String

Dim currentValue As String

currentAnswer = Cell.Value
If questionHeader.Cells(1, questionCounter) <> "" Then
currentQuestion = questionHeader.Cells(1, questionCounter)
End If

pivotSheet.Range("A" & counter).Value = participant

Dim SurveyMonth As Date
SurveyMonth = DateSerial(Year(row.Cells(1, 3)), row.Cells(1, 3), Day(row.Cells(1, 3)))

If row.Cells(1, 3) = "0" Then
pivotSheet.Range("B" & counter).Value = "0"
Else
pivotSheet.Range("B" & counter).Value = MonthName(Month(SurveyMonth))
End If

Dim SurveyYear As Date
SurveyYear = DateSerial(row.Cells(1, 4), Month(row.Cells(1, 4)), Day(row.Cells(1, 4)))
If row.Cells(1, 4) = "0" Then
pivotSheet.Range("C" & counter).Value = "0"
Else
pivotSheet.Range("C" & counter).Value = row.Cells(1, 4)
End If

Dim TrainingMonth As Date
TrainingMonth = DateSerial(Year(row.Cells(1, 5)), row.Cells(1, 5), Day(row.Cells(1, 5)))

If row.Cells(1, 5) = "0" Then
pivotSheet.Range("D" & counter).Value = "0"
Else
pivotSheet.Range("D" & counter).Value = MonthName(Month(TrainingMonth))
End If

Dim TrainingYear As Date
TrainingYear = DateSerial(row.Cells(1, 6), Month(row.Cells(1, 6)), Day(row.Cells(1, 6)))
If row.Cells(1, 6) = "0" Then
pivotSheet.Range("E" & counter).Value = "0"
Else
pivotSheet.Range("E" & counter).Value = row.Cells(1, 6)
End If

Dim URN As String
URN = Range("G3").Value
If row.Cells(1, 7) = "0" Then
pivotSheet.Range("F" & counter).Value = "0"
Else
pivotSheet.Range("F" & counter).Value = row.Cells(1, 7)
End If

Dim HCType As String
HCType = Range("H3").Value
If row.Cells(1, 8) = "0" Then
pivotSheet.Range("G" & counter).Value = "0"
Else
pivotSheet.Range("G" & counter).Value = row.Cells(1, 8)
End If


'CDate(row.Cells(1, 3))


pivotSheet.Range("H" & counter).Value = currentQuestion
pivotSheet.Range("I" & counter).Value = currentAnswer

' TODO Check if the below is 0 and don't print the row if it is
currentValue = row.Cells(1, questionCounter + 8).Value

If IsNumeric(currentValue) = True Then
pivotSheet.Range("J" & counter).Value = currentValue
Else
pivotSheet.Range("J" & counter).Value = 1
pivotSheet.Range("K" & counter).Value = currentValue
End If


counter = counter + 1
questionCounter = questionCounter + 1



Next Cell

currentIndex = currentIndex + 1
questionCounter = 1
Next participant

Application.ScreenUpdating = True

End Sub

Unfortunately, there is no chance that I can attach anything so I know how unhelpful that is.
If there is anyone that can offer any advice, it would be much appreciated

 

[Derek Brown]

Hello and welcome to The Board.

SurveyMonth = DateSerial(Year(row.Cells(1, 3)), row.Cells(1, 3), Day(row.Cells(1, 3)))
' perhaps it should be:
SurveyMonth = DateSerial(Year(row.Cells(1, 3)), Month(row.Cells(1, 3)), Day(row.Cells(1, 3)))

 

[Norie]

What's actually in row.Cells(1, 3) when you get the error?

 

[mondaymorning]

What's actually in row.Cells(1, 3) when you get the error?

Thanks for getting back

Cell (Row, Column). 1st row and Column 3. is Just the field name: Start Date

Thanks for getting back. What I have decided to do is use this piece of work to teach myself Excel VBA! Appreciate you looking into my very vague request for help.

 

[Norie]

Oops, actually meant row.Cells(1,4).

 

[mondaymorning]

Oops, actually meant row.Cells(1,4).

All the first row are field names. Sorry if I am having to be really uhelpful as the file contains info of people not able to share

 

[Norie]

You can't use functions like Year/Day on text.

 

[mondaymorning]

You can't use functions like Year/Day on text.

That sounds very sensible. Any suggestions on how to correct this not from code perspective but from a logic of how to solve. I am happy to work through code myself. But just trying to figure out do I have to define the formats in advance. Thanks again