HELP! VBA Find won't work when copied!

[Blackdeal]

I set up a spreadsheet with a find macro, works no problem. Now I am copying the macro to a new spreadsheet that is set up EXACTLY as the one it is coming from and I cannot get the macro to work. What happened? The text in red below is where the debugger keeps on highlighting.

Sub Find()
Dim sFind As String
Dim rSearch As Range
Dim cl As Range
sFind = Sheets("HDM Find").Range("A1")
With Sheets("EMLT Range Planner")
'set range to search, limit it used cells in range
Set rSearch = .Range(.Cells(1, 3), .Cells(.Rows.Count, 1).End(xlUp))
With rSearch
Set cl = .Find(What:=sFind, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cl Is Nothing Then '< find successful
cl.Offset(0, -1).Copy
Sheets("HDM Find").Range("A2").PasteSpecial
'<- change this to do something with the found cell
Else: MsgBox sFind & " not found"
End If
End With
End With
End Sub

 

[nuked]

I don't think you should have 2 x 'with' statements, otherwise how is Excel supposed to know to which you are referring in your code?


Instead, you could consider using variables.

Dim ws as Worksheet
Dim rng as Range
Dim rng2 as Range

set ws = sheets("something")
set rng = ws.Range("A1:A" & range("A" & rows.count).End(xlUp).Row)
rng2 = rng.Find("Something")



etc. etc.

 

[RoryA]

I would start by removing

After:=ActiveCell

because if the active cell is not in the search range you'll get an error.

 

[Blackdeal]

The other document has the same VBA and it works fine. Could it work in one and not the other?

 

[Blackdeal]

Now it won't find anything. I just get the message box that says "X not found".

 

[RoryA]

What did you change your code to? Also, what are you searching for? (dates are often problematic)

 

[Blackdeal]

Sorry, new at this, forgot to let you know the error I am getting. It is "Run-time error '13': Type mismatch"

I removed the
Code:
After:=ActiveCell

I am searching for strings 5 charaters long in both numeric and alpha-numeric.

 

[Blackdeal]

And don't know if this helps anything, but what I had in red above is what is highlighted in the debugger and the yellow arrow is pointing to the third line.

MatchCase:=False, SearchFormat:=False)

 

[RoryA]

Can you post the whole code as it currently is? (that part you mention is not a complete line and should never be highlighted on its own.)

 

[Blackdeal]

Sub Find()
    Dim sFind  As String
    Dim rSearch As Range
    Dim cl     As Range
    sFind = Sheets("Sheet1").Range("A1")
    With Sheets("EMLT Range Planner").Range("Range")
        'set range to search, limit it used cells in range
            Set cl = .Find(What:=sFind, After:=ActiveCell, LookIn:=xlFormulas, _
                           LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                           MatchCase:=False, SearchFormat:=False)
            If Not cl Is Nothing Then    '< find successful
                cl.Offset(0, -1).Copy
                Sheets("Sheet1").Range("A2").PasteSpecial
                '<- change this to do something with the found cell
            Else: MsgBox sFind & " not found"
            End If
    End With
End Sub