CODEWITHSUNDEEP
vbaexcel
Nilo85
Nilo85

Reputation: 11

Range.Find in VBA Excel

I'm trying to perform a "find" in a Excel sheet with this instruction:

Set Found = Columns(2).Find(What:=value_to_find, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

but I get the error "Run-time error '13': Type mismatch".

If I use Range("A1:H1") instead of Columns(2) I don't get any errors, but I think they are both type Range.

My aim is use a Range variable, at the start of the script I valorize it according to the user's choice.

Upvotes: 0

Views: 13923

Answers (2)

Nilo85
Nilo85

Reputation: 11

[RESOLVED] Many Thanks everyone, I resolved my question with the Ralph's support.

The variable Found (in your code) must be of type variant. So, if you explicitly set Dim Found as variant then your code should work.

Upvotes: 1

whytheq
whytheq

Reputation: 35557

Exactly as @barrowc commented.

This works although not sure of exact requirements:

Sub xxx()

Dim value_to_find As String
value_to_find = "fooBar"

Dim r As Range
Set r = ActiveCell.EntireColumn.Find( _
    What:=value_to_find, _
    After:=ActiveCell, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=False)

MsgBox r.Address

End Sub

enter image description here

Upvotes: 0

Related Questions