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 =)

 

[Kwnstantinos]

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

See if this version of my macro works correctly for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub SelectNonZeroCurrencyCells()
  Dim C As Range, Addr As String
  Set C = ActiveCell.Offset(1)
  If Not C Is Nothing Then
    Addr = C.Address
    Do
      If Val(C.Value) > 0 Then
        If AscW(Right(C.Text, 1)) = 8364 Then
          C.Select
          Exit Sub
        End If
      Else
        Set C = ActiveCell.EntireColumn.Find("*", C, xlValues, xlPart, , xlNext, , False)
      End If
    Loop While Not C Is Nothing And C.Address <> Addr
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Oh my god ! This actually seems to work just fine =D !! Thank you Rick !! One last question ! Your code finds the next cell after the activecell ..Is it possible to predefine (include in macro) which area or column we want to search?

 

[Rick Rothstein]

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

Oh my god ! This actually seems to work just fine =D !! Thank you Rick !! One last question ! Your code finds the next cell after the activecell ..Is it possible to predefine (include in macro) which area or column we want to search?

1) Yes, if you won't mind it always starting from the top of the range every time you open Excel... would that be okay?

2) What did you mean by "area"... a two-dimensional range consisting of more than one row and more than one column as well? If so, how is the search for the next cell to go... across first before down or down first before across?

 

[Kwnstantinos]

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

1) Yes, if you won't mind it always starting from the top of the range every time you open Excel... would that be okay?

2) What did you mean by "area"... a two-dimensional range consisting of more than one row and more than one column as well? If so, how is the search for the next cell to go... across first before down or down first before across?

No i dont mind starting from the top of the range. This is actually what i want. Regarding the search area, i dont really need two-dimensional range (just as an extra tool but not necessarily). I would be fine with predefined row or predifed column