Runtime error 13 - Type Mismatch

[breechd]

I have an excel spreadsheet that has a list of names in Column A (rows 2 through 6). It has a list of categories in Row A (B through X). In my code I identified the person names with NameItem and the categories with CatItem and defined both as Variant in Module 1. I am struggling with why my sheet calculates only into the Unknown column when it seems my code is correct. Any thoughts on how to troubleshoot this issue? Any ideas would be welcome. Thanks much!

 

[schielrn]

It would be very helpful if you posted the code and maybe some sample data of what might be in the cells that the code is going through?

 

[RoryA]

Absolutely no idea what you mean by any of that. Care to try and elaborate a bit?

 

[bobsan42]

maybe post the code is a good idea.

 

[breechd]

Here is the code that is supposed to compare these five names: Robert, Greg, Terri, David, Julie (Rows 2 through 6 in column A). Then compare these categories (top row column B through X): 1:1 Conf Calls, Accident/Injury Follow-up, Attend Required Training, Budget...etc (23 categories in total of tasks these people spent their time on for a number of months). Based on the results of those two comparisons, the spreadsheet is supposed to upload the amount of hours into the correct row and category column for that person.


Public Function AddValue(NameItem As Variant, CatItem As Variant, lngScore As Long) As Boolean

Dim lngRow As Long
Dim lngColumn As Long
Dim lngTargetRow As Long
Dim lngTargetColumn As Long

lngTargetRow = 0
lngTargetColumn = 0

For lngRow = 2 To 6
If Sheet6.Cells(lngRow, 2) = NameItem Then
lngTargetRow = lngRow
End If
Next lngRow
For lngColumn = 2 To 24
If Sheet6.Cells(lngColumn, 2) = CatItem Then
lngTargetColumn = lngColumn
End If
Next lngColumn

If lngTargetRow <> 0 And lngTargetColumn <> 0 Then
Sheet6.Cells(lngTargetRow, lngTargetColumn) = Sheet6.Cells(lngTargetRow, lngTargetColumn) + lngScore
AddValue = True
ElseIf lngTargetRow <> 0 Then
Sheet6.Cells(lngTargetRow, lngColumn) = Sheet6.Cells(lngTargetRow, lngColumn) + lngScore
AddValue = True
ElseIf lngTargetColumn <> 0 Then
Sheet6.Cells(lngRow, lngTargetColumn) = Sheet6.Cells(lngRow, lngTargetColumn) + lngScore
AddValue = True
Else
Sheet6.Cells(lngRow, lngColumn) = Sheet6.Cells(lngRow, lngColumn) + lngScore
AddValue = False
End If
End Function

 

[RoryA]

Are the headers in row 1 or row 2? (Your code checks row 2)

 

[breechd]

See, that's where I get confused when using lngRow and lngColumn identifiers. When looking at the spreadsheet, the categories start in cell B1 and go through X1...hopefully that helps.

 

[RoryA]

Change this:

For lngColumn = 2 To 24
If Sheet6.Cells(lngColumn, 2) = CatItem Then
lngTargetColumn = lngColumn
End If
Next lngColumn

to this:

For lngColumn = 2 To 24
If Sheet6.Cells(1, lngColumn) = CatItem Then
lngTargetColumn = lngColumn
exit for
End If
Next lngColumn

 

[breechd]

rorya,

I am still encountering the same error. It gives me the error "An error has resulted in trying to add this value". Once it runs through all of the rows and columns it then gives me the same Runtime error. Here is the code on the other sheets that feed into the master sheet that I described. These sheets have the name of the person in A1 through A32 and the categories from B1 to X1 (just like the master) and the total time spent per day in each row/column from B2 to X32. There are 26 sheets in total. Here is that code:

Option Explicit

Public Function ProcessSheet()

Dim lngRow As Long
Dim lngColumn As Long

For lngRow = 1 To 32
For lngColumn = 2 To 24
If Sheet11.Cells(lngRow, 1) <> "" And Sheet11.Cells(lngRow, 2) > 0 Then
If Not Module1.AddValue(NameItem:=Sheet11.Cells(lngRow, 1), CatItem:=Sheet11.Cells(lngRow, 2), lngScore:=Sheet11.Cells(lngColumn, 2)) Then
MsgBox "An error resulted in trying to add this value", vbCritical, "Error"
End If
End If
Next lngColumn
Next lngRow
End Function

Thank you so much for assisting me with this. I really need to get this accomplished sooner than later. D

 

[RoryA]

Shouldn't this:

lngScore:=Sheet11.Cells(lngColumn, 2)

be more like:

lngScore:=Sheet11.Cells(1, lngColumn)