Share via

Need help with Excel VBA Range.Find Method

Anonymous
2023-10-19T21:32:03+00:00

Can someone kindly explain to me why Macro1 below runs fine, but Macro2 and Macro3 generate a "Type mismatch" error?

Sub Macro1() 

Columns("B:B").Select

Selection.Find(What:="Blue", After:=ActiveCell, LookIn \_ 

    :=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= \_ 

    xlNext, MatchCase:=False, SearchFormat:=False).Activate

End Sub

Sub Macro2() 

Columns("B:B").Find(What:="Blue", After:=ActiveCell, LookIn \_ 

    :=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= \_ 

    xlNext, MatchCase:=False, SearchFormat:=False).Activate

End Sub

Sub Macro3() 

Range("B1:B1048576").Find(What:="Blue", After:=ActiveCell, LookIn \_ 

    :=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= \_ 

    xlNext, MatchCase:=False, SearchFormat:=False).Activate

End Sub

Thanks in advance for any assistance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-10-19T21:47:41+00:00

    Macro2 and Macro3 will run without error if the active cell is in column B.

    If the active cell is not in column B, the argument After:=ActiveCell causes the error.

    Solution #1: remove After:=ActiveCell:

    For example Macro2:

    Sub Macro2()
    Columns("B:B").Find(What:="Blue", LookIn _
        :=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
End Sub

    Solution #2: specify a cell in column B instead of ActiveCell:

    Sub Macro2()
    Columns("B:B").Find(What:="Blue", After:=Range("B1"), LookIn _
        :=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
End Sub

    2 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-19T22:16:54+00:00

    Thanks, HansV!

    It never occurred to me that After:=ActiveCell was causing the error if the active cell was not in column B, but now it makes sense to me.

    Thanks for educating me.

    Regards, Bob

    0 comments