Type Mismatch error

[JohnM]

Again, I have to say thanks in advance!

In this code, which is below: rowFound = Application.Match(valueToFind,
wrksB.Columns("A:A"), 0)

I get a Type Mismatch error. It worked at one time, then stopped. I am sure
it just needs some adjustment to the code, I am a beginner at this.

Sub FindPlayUpPlayerReplaceAgeGroup()

MsgBox "Change age group on players listed on PlayUpList", vbOKCancel

Dim wrksA As Worksheet
Dim valueToFind As Range
Dim rowNum As Long
Dim wrksB As Worksheet
Dim rowFound As Integer
Dim playUpValue As Range

Set wrksA = Worksheets("PlayUpList")
Set wrksB = Worksheets("PlDetails")
wrksA.Select
rowNum = 2
For rowNum = 2 To wrksA.Cells(Rows.Count, "B").End(xlUp).Row
If Trim(wrksA.Range("b" & rowNum)) <> "" And Trim(wrksA.Range("v" &
rowNum)) <> "" Then
Range("B" & rowNum).Select
Set valueToFind = Selection
' MsgBox valueToFind
Range("a" & rowNum).Select
Set playUpValue = Selection
' MsgBox playUpValue
rowFound = Application.Match(valueToFind, wrksB.Columns("A:A"),
0) ' <= displays error
' MsgBox rowFound
wrksB.Range("u" & rowFound) = playUpValue
' Range("v" & rowFound) = playUpValue
End If
Next

MsgBox "All players playing up have been modified."

End Sub

 

[Barb Reinhardt]

Try this (untested)

Option Explicit
Sub FindPlayUpPlayerReplaceAgeGroup()

MsgBox "Change age group on players listed on PlayUpList", vbOKCancel

Dim wrksA As Worksheet
Dim valueToFind As Range
Dim rowNum As Long
Dim wrksB As Worksheet
Dim rowFound As Integer
Dim playUpValue As Range

Set wrksA = Worksheets("PlayUpList")
Set wrksB = Worksheets("PlDetails")
rowNum = 2
For rowNum = 2 To wrksA.Cells(Rows.Count, "B").End(xlUp).Row
If Trim(wrksA.Range("b" & rowNum)) <> "" And _
Trim(wrksA.Range("v" & rowNum)) <> "" Then

Set valueToFind = wrksA.Range("B" & rowNum)

' MsgBox valueToFind
Set playUpValue = wrksA.Range("A" & rowNum)

' MsgBox playUpValue
rowFound = 0
On Error Resume Next
rowFound = Application.Match( _
valueToFind.Text, _
wrksB.Columns("A:A"), _
0) ' <= displays error
On Error GoTo 0
If rowFound = 0 Then
MsgBox ("Didn't find row")
Else
wrksB.Range("u" & rowFound) = playUpValue
End If
End If
Next

MsgBox "All players playing up have been modified."

End Sub

 

[JLGWhiz]

I believe that if you change this:

Set valueToFind = Selection

To this:

valueToFind = Selection.Value

then in might work.

The problem is caused by delclaring rowFound as an integer and then trying
to match it to valueToFind which was Set as an object variable range. If
the Selection value is an integer then the Match function should work after
you make the change.

 

[JohnM]

Barb, thanks for the code. With it I figured out it wasn't really a Type
Mismatch, my data was wrong, so the error test is what lead me to my problem
and it makes the program much better.

Thanks again,

 

[Dave Peterson]

Change
Dim rowFound As Integer
to
Dim rowFound As Variant 'could be an error.

Then check for that error:

rowFound = Application.Match(valueToFind, wrksB.Columns("A:A"), 0)
if iserror(rowfound) then
msgbox "Not found!"
else
' MsgBox rowFound
wrksB.Range("u" & rowFound) = playUpValue
' Range("v" & rowFound) = playUpValue
...
end if

======
Ps. I never use "As Integer". "As Long" handles bigger numbers and from what
I've read Long's are faster than integers.

I never use "As Single" either. I'll use "as double".

(In this case, you still want "As Variant" because of the possibility of no
match found.