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.

The cell actually has the euro symbol in it as a value and not just formatted as the euro currency? e.g. You have typed `€1.00` into the cell, rather than you typed `1` and the cell is displaying `1.00 €`? One instance you're searching for the symbol, the other you're searching the number format - which appears a little weird for euros. — Darren Bartrup-Cook, Feb 06 '18 at 14:15
@DarrenBartrup-Cook The odd thing is when you search manually (Ctrl + F) for the € symbol it doesn't matter if it is in the format or in the cells value itself. But if you do the same with VBA it only finds the symbol in the value but not in the format. So I assume a bug here (tested with Excel 2016 x64). — Pᴇʜ, Feb 06 '18 at 14:23
@Peh. You're right with the Ctrl+F. Recording a macro seems to change the euro depending on where the currency is - `€ Breton` is `"#,##0.00 [$€-47E]"` while `€ Galician` is `"#,##0.00 [$€-456]"`. This code finds the format but only the Breton euro: `Application.FindFormat.Clear: Application.FindFormat.NumberFormat = "#,##0.00 [$€-47E]": Set x = Cells.Find(What:="*", SearchFormat:=True)` — Darren Bartrup-Cook, Feb 06 '18 at 14:30
Yes it is actually formatted as a euro currency but still it works perfectly when seaching manually.. — Kostantinos Kalominidis, Feb 06 '18 at 14:34
What's the Symbol you use in the Currency selection? If you go into format it should say something like `€ Dutch (Belgium)`. — Darren Bartrup-Cook, Feb 06 '18 at 14:41

Vityata · Answer 1 · 2018-02-06T13:42:48.180

0

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

edited Feb 06 '18 at 13:42

answered Feb 06 '18 at 13:08

Vityata

42,633
8
55
100

Vityata thank you very much for your reply. I also suspect that Activecell is the problem but using ''Chr(128)'' instead of ''InStr(ActiveCell.NumberFormat, Chr(128))'' gives me run-time error 91 . It seems like it cant see the symbol at all – Kostantinos Kalominidis Feb 06 '18 at 13:36
@KostantinosKalominidis - try again. – Vityata Feb 06 '18 at 13:37
Vityata thank you for your time. I tried and same run-time error 91 :( – Kostantinos Kalominidis Feb 06 '18 at 13:55
@Vityata if it finds nothing then `.Select` fails. Also if the euro symbol is part of the format (formatted as currency) this might fail. This seems to be an Excel bug: You can search for the Euro symbol in the format when you do it manually but it fails if you do it with VBA. – Pᴇʜ Feb 06 '18 at 13:57

score 0 · Answer 2 · answered Feb 06 '18 at 14:51

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.

Thank very much Daren for trying to help me. I dont know how can i adapt the code you sent for making it fit my need. I am only trying to make a macro button that will automatically select column H and then search and select one by one in an array(one every time i click the macro) every cell in that specific column, that contains the € symbol. Exactly as i can do manually using the native excel search function. Yet i dont know how to do that ..Note that the cells in column H are currency formatted.. — Kostantinos Kalominidis, Feb 07 '18 at 13:03

Search for € symbol in a range defined in code

2 Answers2

Linked