VBA-Type Mismatch on .Find function.

[JediJim]

Guys,

I have a wall regarding my Macro. Bascially I have created functions, and the .find function which was previously working, now states type mismatch-specifically vOurResult code at the end is the problem

The whole code is this:


Function TextSearch()
'(ByVal strSearchFor, ByVal Target As Variant)

Sheets("MO Trade List").Select
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
With Range("A2:A13")
Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = Range("A2:A13").Find(What:="TCPAM Investments (Cayman) Limited - MENA Equity Fund", AFTER:=LastCell)
'Cells.FindNext(After.ActiveCell).Activate
'Cells.FindPrevious(After.ActiveCell).Activate

If Not FoundCell Is Nothing Then
FirstAddr = FoundCell
End If

Do Until FoundCell Is Nothing
FoundCell.Copy
Range("A20").Select
ActiveSheet.Paste
'If FoundCell. Found Then Do. FindNext(After:=FoundCell)
Set FoundCell = Range("A2:A13").FindNext(AFTER:=FoundCell)
FoundCell.Copy
Range("A21").Select
ActiveSheet.Paste
Set FoundCell = Range("A2:A13").FindNext(AFTER:=FoundCell)
FoundCell.Copy
Range("A22").Select
ActiveSheet.Paste
'Set FoundCell = Range("A2:A13").FindPrevious(After:=FoundCell)
If FoundCell = FirstAddr Then
Exit Do
End If
Loop









'If Not .Found Then Exit Do
'Loop Until Not .Found

End Function

'Fund Name
Function ValueCatching()
Sheets("MO Trade List").Select
Dim Fund1 As String

vOurResult = Cells.Find(What:="TCPAM Investments (Cayman) Limited - MENA Equity Fund", AFTER:=LastCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)


Please advise?

Thanks

JJ

 

[Jon von der Heyden]

Is the problem in here?

Function ValueCatching()
Sheets("MO Trade List").Select
Dim Fund1 As String

vOurResult = Cells.Find(What:="TCPAM Investments (Cayman) Limited - MENA Equity Fund", AFTER:=LastCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)

You've told it to find after LastCell but no where prior to this is there a range object variable set to any specific range.

 

[JediJim]

Is the problem in here?

Function ValueCatching()
Sheets("MO Trade List").Select
Dim Fund1 As String
 
vOurResult = Cells.Find(What:="TCPAM Investments (Cayman) Limited - MENA Equity Fund", AFTER:=LastCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)

You've told it to find after LastCell but no where prior to this is there a range object variable set to any specific range.

Hi Jon-Thanks for the response, I actually just got it working again. I replaced LastCell with Cells (1, 1)-and for some reason this worked today buit didnt when I tried it previously.

Cheers anyway.

MOD can delete the thread

JJ