Search for € symbol in a range defined in code

Question

I am not experienced in VBA coding.

My VBA code:

Search = InStr(ActiveCell.NumberFormat, Chr(128))
Selection.Find(What:=Search, After:=ActiveCell, LookIn:=xlValues, _
  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False, SearchFormat:=False).Activate

It searches and activates the cells which include the € symbol. It works only if I manually define my selection range.

When I try inserting ActiveSheet.Range("H:H").Select to make column H my selection (which is my goal), the code stops working.

Answer 1

This code found the cell with the Greek Euro format in the range A1:A6 on Sheet1 in the workbook containing the code (ThisWorkbook).
The cell must hold a value (to find blanks change "*" to "").

Sub Test()

    Dim rRangeToSearch As Range
    Dim rFoundRange As Range

    Set rRangeToSearch = ThisWorkbook.Worksheets("Sheet1").Range("A1:A6")

    Application.FindFormat.Clear
    Application.FindFormat.NumberFormat = "#,##0.00 [$€-408]"
    Set rFoundRange = rRangeToSearch.Find(What:="*", SearchFormat:=True)

    If Not rFoundRange Is Nothing Then
        MsgBox "Greek Euro format found in cell " & rFoundRange.Address
    End If

End Sub

No idea why [$€-408] denotes Greek.

Answer 2

The problem is in the ActiveCell, which is changing depending on what you are selecting. Try like this, you should get lucky:

Option Explicit

Sub TestMe()

    Dim Search As String

    ActiveSheet.Range("H:H").Select
    Search = CStr(Chr(128))
    Selection.Find(What:=Search, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Select 'or .Activate

End Sub

Once you feel a bit better with recording macros, you may try to avoid ActiveSheet, Selection and ActiveCell:

How to avoid using Select in Excel VBA