Running a VBA Subroutine resulted in a Runtime Error "13"

[DonEB]

I ran the following subroutine and it resulted in a Run-time error "13", Type Mismatch. I highlighted the part of the code where the program stopped and gave me the message.

Might anyone have a thought on what the problem might be or something else I might be able to try?

In case you need to know, AO6:AO25 contains a set of numbers that may range from 1 - 30 and could hold anywhere from 5 to a max of 20 numbers. Also, A4:AH34 is a chart that contains another set of names and numbers that is ultimately used in another part of a larger program. All I'm trying to do here is to change numbers in Row 3 (E3:AH3) and Column A (A6:A34) to 1 if a player's number is found in the chart located in AO6:AO25. Then sort it from top to bottom and left to right in order to pull all the active players together.

Putting all the details aside, I'm really just trying to figure out why I'm getting the Run-time error "13" on the line of code below. And I'm at a total loss of what might be the problem.

I appreciate anyone willing to look at this and provide some guidance.

Sub PreComboSort()
' ******************************
' * This is to Sort numbers based on who's been chosen to play
' ******************************
Dim Player As Long
Dim LastRowa As Long
Dim LastRowb As Long
Dim A As Long
Dim b As Long
Dim i As Long
Dim ii As Long
LastRowa = Sheets("CommonData2").Range("AO" & Rows.Count).End(xlUp).Row
LastRowb = Sheets("CommonData2").Range("C" & Rows.Count).End(xlUp).Row

' In the respective rows marked players who will be playing with the number 1
For i = 6 To LastRowa
Player = Sheets("CommonData2").Range("AO" & i).Value '<===== Run-time error '13'
For ii = 6 To LastRowb
If Sheets("CommonData2").Range("B" & ii).Value = Player Then
Sheets("CommonData2").Range("A" & ii).Value = "1"
End If
Next ii
Next i

Dim PlayerR As Long
Dim LastRow As Long
Dim ir As Long
Dim counter As Integer

LastRow = Sheets("CommonData2").Range("AO" & Rows.Count).End(xlUp).Row

' In the respective columns, marked players who will be playing with the number 1
For ir = 6 To LastRow
PlayerR = Sheets("CommonData2").Range("AO" & ir).Value

For counter = 5 To 34
If Worksheets("CommonData2").Cells(4, counter).Value = PlayerR Then
Worksheets("CommonData2").Cells(3, counter).Value = 1
End If
Next counter
Next


' Following code sorts chart from top to bottom
Dim rng2 As Range
Set ws2 = Worksheets("CommonData2")
Set rng2 = ws2.Range("A6:AL34")

ws2.Sort.SortFields.Clear

With rng2
.Sort Key1:=ws2.Range("A6"), Order1:=xlAscending, _
Header:=xlNo
End With

' Following code sorts chart from left to right
ws2.Sort.SortFields.Clear
ws2.Range("E3:AH34").Sort Key1:=ws2.Range("E3:AH34"), Order1:=xlAscending, Orientation:=xlLeftToRight

End Sub

 

[Micron]

Are you saying the meaning of the message is not clear enough to point you in the right direction? If so, it means that you're trying to pass a data type that is not allowed to be in your variable e.g. text to a number variable, a date to an integer, Null to anything but a variant, etc. This can happen in the first or last pass in a loop or anywhere in between. Because of that I'd try using an error handler to debug.print the value and its data type when error 13 is raised.

could hold anywhere from 5 to a max of 20 numbers.

If you're saying that you are trying to pass multiple numbers to Player, then that has to be it. Only a string or array or an object such as a collection or dictionary can hold multiple values.

Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.

 

[DonEB]

Are you saying the meaning of the message is not clear enough to point you in the right direction? If so, it means that you're trying to pass a data type that is not allowed to be in your variable e.g. text to a number variable, a date to an integer, Null to anything but a variant, etc. This can happen in the first or last pass in a loop or anywhere in between. Because of that I'd try using an error handler to debug.print the value and its data type when error 13 is raised.

If you're saying that you are trying to pass multiple numbers to Player, then that has to be it. Only a string or array or an object such as a collection or dictionary can hold multiple values.

Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.

Micron,
Thanks for your input and guidance. Yes... I failed to understand the meaning of the error message, but your brief explanation clarified it for me. Once I knew what to look for then a quick "MsgBox" provided me with what my problem was and then a solution was found.

Once again, thank you for your help.

 

[Micron]

It's common practice to post your solution so that others may benefit from it. That would be nice.

 

[DonEB]

It's common practice to post your solution so that others may benefit from it. That would be nice.

Micron,
I'll do my best.

To start, while I didn't use the debug.print, I did insert a "Msgbox" in order to print the value that was being stored in a variable I was using and where the Run-time error message of 13 was stopping. The Msgbox was simple and was as follows: MsgBox ("From PreComboSort: ") & Sheets("CommonData2").Range("AO" & i).Value. What this did for me was to prove that the number of iterations that were being performed was far greater than I was expecting. And this led to the discovery of the real problem.

I was using the following formula to determine what I thought was going to be the last value in a column. Unfortunately, I have 4 more charts located directly below the one I was examining, and I mistakenly thought the code would stop looking once it reached blank cell.
This code did not work as I had hoped: LastRowa = Sheets("CommonData2").Range("AO" & Rows.Count).End(xlUp).Row

Upon further investigation, I discovered some other code that seems to have done the task I was hoping to perform previously. The new code being used is ...
LastRowa = Sheets(wsName2).Range("AO6:AO25").Find(What:="*", After:=Sheets(wsName2).Range("AO6:AO25").Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

While I now have to define a range, it will also give me the last row where data can be found. Since this value is used to determine how many iterations are performed (i.e., For i = 6 To LastRowa), this effectively helps speed up the process. The larger the chart, the bigger the impact