新聞財經娛樂♬資訊 News Economy

Martin's vba code was simple and worked, so I gave him points. My own code is more complicated by much faster so I gave myself the best answer.

Duncan's attempt avoided VBA. so it would have been ideal. Unfortunately, it did not work if any of the numbers had decimals, commas or other formatting.

I simplified my code, then did a little more testing.  It turns out that Martin's code is faster than my code when more than 20% of the cells  have ###,   For instance, when I had 20000 cells which ALL had ###, My code took 5 times longer than Martin's.  For my purposes, this rarely happens, so I will continue to use my own code. Nonetheless, Martin's might be "better" for other people.


Sub CountPoundSigns()
 Dim time As Double
 time = Now()   '  AccurateNow()  '  < my personal.xls has an AccurateNow function which is far more accurate than Now().


 Dim cell As Object, cellA As Range, cnt As Long

 With ActiveSheet.UsedRange.Columns(1)
     
     Set cellA = .find(what:="#", LookIn:=xlValues, LookAt:=xlPart, _
            matchcase:=False, SearchOrder:=xlByRows)
   
     If Not cellA Is Nothing Then
        Set cell = cellA
         Do
            cnt = cnt + 1
            Set cell = .FindNext(cell)
         Loop While cell.Address <> cellA.Address
     End If
 End With

 MsgBox cnt & " " & (Now()- time) * 24 * 60 * 60

 End Sub

On my machine your code took 6 seconds for 20000 rows.
my code took less than a second.

When I originally posted this question, I had not actually implemented my workaround, so I did not realize how fast it would be.  It turns out it is blazing fast, so I have actually accomplished what I wanted.

I will close the question.

Doing it on Excel cell that is not easy one for your case even I tried it to closer one. I suggest you
to do it on VBA  to pick easier way

Have a nice day , talk your tomorrow

Duncan

I tried that works at my side  no any issue at all

Just open a new sheet to test  those suggestion from the posts

Duncan

Mine took 1.5 seconds for 100,000 records. Out of curiosity how long did your's take? BTW if your interested I have an article on timing code if you want an accurate number.

It cannot work reliably. Demonstrate yourself.  Put 123456 into cell a1 and a2. Format a1 as $#,##0.00 and a2 as #0

You will see a1 displays ###  and a2 displays 123456.  But then both have len = 6

so len() is not related to the cell formated or not, just
make simple example on a new sheet and test my code for Excel cell before
concluding it

Duncan

Please let us know what you want to let it work on Excel cell formula  or VBA ?

it seems VBA is better, and the speed is not such slow

Duncan: I should have mentioned the cells are formatted, so Len function will not work at all.

Martin, the goal was something faster than my workaround. Your solution is simpler but a LOT slower.

I suspect that there is no solution that is better than mine, but I will leave the problem open for a while longer in hopes of another approach.

rberke

Sub CountPoundSigns()
Dim rng As Range


Dim cell As Object, firstaddress As String, fnd As Range


With ActiveSheet.UsedRange.Columns(1)
Set cell = .Find(what:="#", LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False, SearchOrder:=xlByRows)

If Not cell Is Nothing Then
    firstaddress = cell.Address
    Do
        If fnd Is Nothing Then
            Set fnd = cell
        Else
            Set fnd = Union(fnd, cell)
        End If
        Set cell = .FindNext(cell)
   
    Loop While cell.Address <> firstaddress
End If
End With

MsgBox fnd.Cells.Count
Exit Sub
End Sub

It may be better doing on VBA

For Excel cell,I try to compare the length of cell and cell width to solve the issue that is  what I can do but that is not good one if narrow width is equal to one character,  If length of cell is greater than cell width, it means the cell has pound sign(#).
please try it  as follows for at least narrow width=2.For narrow width=1, it will work sometimes and
sometimes not. So you need to work around for this


=SUMPRODUCT(--(LEN(A1:A5)>CELL("width",A1)))

Be reminded need to do automatic update cell formula for refresh cell if the columnwidth is changed by Excel: tools->options.caluation->calcuation->automatic

Hope understand your question completely.If not, please point it out
Duncan