VBA Excel find function for currency formatted cells

[Kwnstantinos]

I am not experienced in VBA coding at all and I have a seemingly simple question. I would like to create a click button macro which will find and select every currency formated cell which includes the euro symbol € or every currency formatted cell that has a sum above 0 within a specific column. At the moment this code :


Cells.Find(What:="€", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


https://imgur.com/a/IRZvF
is close to what I want to achieve (finds the € symbol) but it does not work on currency formatted cells which by default-automatically add the euro symbol to the sum..and is not defined to a specific column. If for example in my worksheet only some currency formatted cells have a € symbol (which I am looking for), then macro gives me a Run-time error 91..The same exactly search parameters work fine using the native excel search function.


See pictures attached.
Please kindly advise, it will help me a lot with my project =)

 

[Roderick_E]

Re: VBA Excel find function for currency formatted cells - Please advise

I am not experienced in VBA coding at all and I have a seemingly simple question. I would like to create a click button macro which will find and select every currency formated cell which includes the euro symbol € or every currency formatted cell that has a sum above 0 within a specific column. At the moment this code :


Cells.Find(What:="€", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


https://imgur.com/a/IRZvF
is close to what I want to achieve (finds the € symbol) but it does not work on currency formatted cells which by default-automatically add the euro symbol to the sum..and is not defined to a specific column. If for example in my worksheet only some currency formatted cells have a € symbol (which I am looking for), then macro gives me a Run-time error 91..The same exactly search parameters work fine using the native excel search function.


See pictures attached.
Please kindly advise, it will help me a lot with my project =)

You'll need to check the .NumberFormat for the use of chr(128) which is the euro symbol

if InStr(ActiveCell.NumberFormat, Chr(128))>0 then
'do something
endif

 

[Kwnstantinos]

Re: VBA Excel find function for currency formatted cells - Please advise

You'll need to check the .NumberFormat for the use of chr(128) which is the euro symbol

if InStr(ActiveCell.NumberFormat, Chr(128))>0 then
'do something
endif

Thank you Roderick for your reply. I adjusted InStr(ActiveCell.NumberFormat, Chr(128)) in my previous searching code and it found the euro symbol !!!(excited) but my macro only works if i previously select the column that i want to search (In my case i need column H). In case i include the ActiveSheet.Range("H:H").Select in the code below the macro stops working. Please advise =)

Sub FINDER()

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


End Sub

 

[Kwnstantinos]

Re: VBA Excel find function for currency formatted cells - Please advise

Thank you Roderick for your reply. I adjusted InStr(ActiveCell.NumberFormat, Chr(128)) to my previous searching code and it found the euro symbol !!!(i was excited) but my macro only works if i previously select manually the column that i want to search (In my case i need column H). In case i include the ActiveSheet.Range("H:H").Select in the code below the macro stops working. Please advise =)

Sub FINDER()

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


End Sub

 

[Roderick_E]

Re: VBA Excel find function for currency formatted cells - Please advise

Sorry for the delay.
Your Search is returning a number, whereas your selection.find(What:= was previously looking for a symbol. I'm a little confused why you need the Selection.find anymore. Are you only expecting the euro symbol once in the entire column H? Maybe I'm missing you loop you're doing?

 

[Kwnstantinos]

Re: VBA Excel find function for currency formatted cells - Please advise

Sorry for the delay.
Your Search is returning a number, whereas your selection.find(What:= was previously looking for a symbol. I'm a little confused why you need the Selection.find anymore. Are you only expecting the euro symbol once in the entire column H? Maybe I'm missing you loop you're doing?

Thank very much Roderick 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..

 

[Roderick_E]

Re: VBA Excel find function for currency formatted cells - Please advise

Hello, now I understand. Try this macro. Assign it to a button. It will select the next euro formatted cell in column H every time you press it.

Sub euroselector()
If Not Intersect(Selection, Columns("H")) Is Nothing Then
    If Intersect(Selection, Columns("H")).Address = Columns("H").Address Then
    lastrow = Columns("H").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
     For x = 1 To lastrow
     If InStr(Cells(x, "H").NumberFormat, Chr(128)) > 0 Then
     Cells(x, "H").Select
     Exit Sub
     End If
     Next x
        Else
lastrow = Columns("H").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
For x = ActiveCell.Row + 1 To lastrow
     If InStr(Cells(x, "H").NumberFormat, Chr(128)) > 0 Then
     Cells(x, "H").Select
     Exit Sub
     End If
     Next x
End If
Else
MsgBox "Select column H or a cell in column H to begin", vbInformation, "ALERT"
End If
End Sub

 

[Kwnstantinos]

Re: VBA Excel find function for currency formatted cells - Please advise

Hello, now I understand. Try this macro. Assign it to a button. It will select the next euro formatted cell in column H every time you press it.

Sub euroselector()
If Not Intersect(Selection, Columns("H")) Is Nothing Then
    If Intersect(Selection, Columns("H")).Address = Columns("H").Address Then
    lastrow = Columns("H").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
     For x = 1 To lastrow
     If InStr(Cells(x, "H").NumberFormat, Chr(128)) > 0 Then
     Cells(x, "H").Select
     Exit Sub
     End If
     Next x
        Else
lastrow = Columns("H").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
For x = ActiveCell.Row + 1 To lastrow
     If InStr(Cells(x, "H").NumberFormat, Chr(128)) > 0 Then
     Cells(x, "H").Select
     Exit Sub
     End If
     Next x
End If
Else
MsgBox "Select column H or a cell in column H to begin", vbInformation, "ALERT"
End If
End Sub

Hello again Roderick ! Your code when used give me the message "Select column H or a cell in column H to begin" but even when i manuallu select column H it does not select the cells that contain the euro symbol..

 

[Roderick_E]

Re: VBA Excel find function for currency formatted cells - Please advise

So you select column H THEN click the button? Also, if column H merged with another column?

 

[Kwnstantinos]

Re: VBA Excel find function for currency formatted cells - Please advise

So you select column H THEN click the button? Also, if column H merged with another column?

No column H is not merge with any other column. Wghen i click the button with your code, it gives me the message "Select column H or a cell in column H to begin" but even when i manually select column H (as the message says) it still does not find the cells that contain the euro symbol