Can't find "$" in currency formatted cell

[scubadivingfool]

I am trying to use the below code to find the cells with a "$" in it. When I manually place the "$" in a cell it works perfectly but when the cell is formatted for currency it does not found anything.

Any help is appreicated.

Sub Copy_To_Another_Sheet_1()
    Dim FirstAddress As String
    Dim MyArr As Variant
    Dim Rng As Range
    Dim Rcount As Long
    Dim I As Long
    Dim NewSh As Worksheet

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

  
    MyArr = Array("$")

    'Add new worksheet to your workbook to copy to
    'You can also use a existing sheet like this
    Set NewSh = Sheets("Sheet2")
    
    With Sheets("Sheet1").Range("A1:Z100")

        Rcount = 0

        For I = LBound(MyArr) To UBound(MyArr)

            Set Rng = .Find(What:=MyArr(I), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                FirstAddress = Rng.Address
                Do
                    Rcount = Rcount + 1
                    Rng.Copy NewSh.Range("A" & Rcount)
                    Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        Next I
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[alansidman]

This link should help. Use Number Format to find your data

Accounting Number Format through VBA Formatting

Let’s take an example to understand how and where we can format the cell for accounting. We have product’s amount data in range A1:B9 in which column A contains Product name and column B contains amount of it. We want … Continue reading →

www.exceltip.com

 

[scubadivingfool]

It seems the link is more for adding a "$" to cells that just have numbers in it and not being about to search for cells that have the "$" in it as a result of being formatted with the currency format

 

[alansidman]

Yes, but it is the concept of how to format, so you need to reverse engineer it as you are looking for data that is formatted in that manner. You apparently have VBA skills that will allow you to do that.

 

[jasonb75]

not being about to search for cells that have the "$" in it as a result of being formatted

The cell doesn't have $ in it as the result of being formatted, it still only has the original value. Think of the formatting as a transparent overlay that changes the appearance of whatever is behind it.

Using ctrl f to search the sheet gives you an option to search by format but for whatever reason this errors when you try to repeat the same steps in vba.

As with many things, there is possibly a more practical way to do what you are trying to do, such as looking for intersections of specific row and column headers.