Runtime error 13 - Type Mismatch

breechd

New Member
Joined
Jul 16, 2010
Messages
14
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!
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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?
 
Upvote 0
Absolutely no idea what you mean by any of that. Care to try and elaborate a bit? :)
 
Upvote 0
maybe post the code is a good idea.
 
Upvote 0
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
 
Upvote 0
Are the headers in row 1 or row 2? (Your code checks row 2)
 
Upvote 0
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.
 
Upvote 0
Change this:
Code:
For lngColumn = 2 To 24
If Sheet6.Cells(lngColumn, 2) = CatItem Then
lngTargetColumn = lngColumn
End If
Next lngColumn
to this:
Code:
For lngColumn = 2 To 24
If Sheet6.Cells(1, lngColumn) = CatItem Then
lngTargetColumn = lngColumn
exit for
End If
Next lngColumn
 
Upvote 0
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 :)
 
Upvote 0
Shouldn't this:
Code:
lngScore:=Sheet11.Cells(lngColumn, 2)
be more like:
Code:
lngScore:=Sheet11.Cells(1, lngColumn)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top