PDA

View Full Version : [SOLVED] Type Mismatch Error Confusion



cosmarchy
05-09-2016, 01:08 PM
Hi,
I have the following function which is supposed to find text in a specific column after a specific cell:

Public Function FindTextRowInColumn(strText As String, oWorksheet As Worksheet, intColumn As Integer, Optional LookAfter As Range) As Long

On Error GoTo LastUsedColumn_err
Dim c As Range

On Error Resume Next
If (LookAfter Is Nothing) Then Set LookAfter = oWorksheet.Cells(10, 1)

Set c = oWorksheet.Columns(intColumn).Find(strText, oWorksheet.Range("A10:B10"), xlValues, xlWhole, xlByColumns, xlNext) '< Error here

If (Not c Is Nothing) Then
FindTextRowInColumn = c.Row
Else
FindTextRowInColumn = 0
End If

LastUsedColumn_exit:
Exit Function

LastUsedColumn_err:

End Function

The trouble is I am getting a Type Mismatch error on the line which begins " Set c = ....." as indicated above.
I cannot see why. I believe the problem lies in the after clause, which in this case is oWorksheet.Range("A10:B10") because if I remove it, it all works fine. This clause returns a range which the after clause requires so I cannot see what the problem is.

Can anyone shed some light on this one please?

Thanks

PAB
05-09-2016, 01:41 PM
Hi cosmarchy,

Give this a go...


Public Function FindTextRowInColumn( _
strText As String, oWorksheet As Worksheet, intColumn As Integer, Optional LookAfter As Range) As Long

Dim c As Range

On Error GoTo LastUsedColumn_err
On Error Resume Next

If (LookAfter Is Nothing) Then
Set LookAfter = oWorksheet.Cells(10, 1)
End If

Set c = oWorksheet.Columns(intColumn).Find(strText, oWorksheet.Range("A10:B10"), _
LookIn:=xlValues, LookIn:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

If (Not c Is Nothing) Then
FindTextRowInColumn = c.Row
Else
FindTextRowInColumn = 0
End If

LastUsedColumn_exit:
Exit Function
LastUsedColumn_err:
End Function

I hope this helps!

cosmarchy
05-09-2016, 01:48 PM
Hi Pab,

Strange... I now get an odd error on the same line "run-time error 448 named argument not found"

Never seen that one before!!

cosmarchy
05-09-2016, 01:56 PM
Ok, so there is something a bit weird going on here.

If I use the code Pab provided and change that one single line with the one from my original code, the error changes between "named argument not found" and "type mismatch" but the only difference is that Pab's code explicitly names the function parameters whereas I didn't, yet there are errors :crying: different ones at that!!

a bit strange, I must say; I would have expected the same error especially as the code is essentially the same thing.

PAB
05-09-2016, 02:00 PM
OK, give this a go...


ublic Function FindTextRowInColumn( _
strText As String, oWorksheet As Worksheet, intColumn As Integer, Optional LookAfter As Range) As Long

Dim c As Range
On Error GoTo LastUsedColumn_err
On Error Resume Next
If (LookAfter Is Nothing) Then
Set LookAfter = oWorksheet.Cells(10, 1)
End If

Set c = oWorksheet.Columns(intColumn).Find(strText, oWorksheet.Range("A10:B10"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

If (Not c Is Nothing) Then
FindTextRowInColumn = c.Row
Else
FindTextRowInColumn = 0
End If

LastUsedColumn_exit:
Exit Function

LastUsedColumn_err:
End Function

cosmarchy
05-09-2016, 02:05 PM
this time it's back to the good ol 'trusty' type mismatch error..... :doh:

PAB
05-09-2016, 02:07 PM
Have you tried it without the line?


SearchDirection:=xlNext

PAB
05-09-2016, 02:18 PM
Was this code created in a different version of Excel than you are trying to run it on?
I only say this because latter versions have added some new arguments to the FIND functionality.

Have you tried recording the sequence manually and looking at the code produced to see if there are any missing arguments.

You should get something like this...


Selection.Find( _
What:="dog", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

Just a thought!

cosmarchy
05-09-2016, 02:27 PM
It's all developed and run in the same version of excel 2007.

I'll give the macro a go and see what I get......

Strange one this.

PAB
05-09-2016, 02:30 PM
I'll give the macro a go and see what I get...

OK, perhaps you could then post your results so we could better identify the problem.

cosmarchy
05-09-2016, 02:35 PM
Recorded macro comes back with this:

Cells.Find(What:="sadfsf", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Now the obvious one that is missing is LookIn which I removed from the code in your latest post and I still get the Type Mismatch error.

If I remove the oWorksheet.Range("A10:B10") from your latest code, the error goes away just as it does in my original code.
So, it seems as if the error is related to the After parameter which I cannot understand.....:think:

PAB
05-09-2016, 02:39 PM
Does the LookIn:=xlValues not make a difference?

SamT
05-09-2016, 03:49 PM
oWorksheet.Columns(intColumn).Find(strText, oWorksheet.Range("A10:B10")
That says: Starting after Range("A10:B10") in some random column, find the text.

The After:= Parameter must be a single cell and it must be a cell in the Range you are searching.

cosmarchy
05-10-2016, 11:14 AM
That says: Starting after Range("A10:B10") in some random column, find the text.

The After:= Parameter must be a single cell and it must be a cell in the Range you are searching.

Came to the same conclusion very late last night. only just had the opportunity to test this thoroughly and I believe I have it buttoned up now.

Thanks guys for your assistance :)

PAB
05-10-2016, 11:40 AM
Did you use something like...


oWorksheet.UsedRange("A10")?