VBA Find function ignores percentages -> only finds decimals

Question

My macro searches all open workbooks for the active cell value (e.g. 98%). However, it only finds the value 0.98 but not the value 98% in other cells. Why?

Here is my macro:

Sub FindIt2()
Dim wSheet As Worksheet
Dim wBook As Workbook
Dim rFound As Range
Dim firstAddress As String
lookfor = Selection.Value

On Error Resume Next
For Each wBook In Application.Workbooks
    For Each wSheet In wBook.Worksheets

        Set rFound = wSheet.Cells.Find(What:=lookfor, After:=wSheet.Cells(1, 1), _
        LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)

        If Not rFound Is Nothing Then

            firstAddress = rFound.Address

            Do

                Application.Goto rFound, True

                MsgBox "The Search String has been found these locations: "

                Set rFound = wSheet.Cells.FindNext(rFound)

            Loop While Not rFound Is Nothing And rFound.Address <> firstAddress

        End If

    Next wSheet

Next wBook
On Error GoTo 0

Anyone has an Idea how to solve this? Thanks!

EDIT: I want it to find both: 98% and 0.98

You never define the variable that your search value is in. So it will by definition become an integer if I remember correctly. — Luuklag, Jul 30 '17 at 11:16
yes, you are correct that I don't define it. However, it does not seem to become an integer as I find 0.98. What would be the appropriate definition? I use the macro for many different types of values.. — Lennart, Jul 30 '17 at 11:19
I think best would ve to convert the values in the worksheet to either all fractions or all percentages. Otherwise you end up checking against two variables. — Luuklag, Jul 30 '17 at 11:26
Why `On Error Resume Next`? If you need that for your code to run, you should do something about the errors rather than hide them. — John Coleman, Jul 30 '17 at 11:27
If both `0.98` and `98%` are stored in Excel as numeric values (usually the case), they will **BOTH** be stored as `0.98` and the only difference will be the formatting. So you will have to look at that in order to differentiate. In addition, if either value is the result of a calculation, it will likely not be `0.98` exactly, but would be being displayed that way. So you would have to search for the rounded value, or examine the `.text` property of the range object and not the `value` property. Hard to tell without your actual data. — Ron Rosenfeld, Jul 30 '17 at 11:42
@RonRosenfeld - Strange as it seems (it surprised me, as I never paid attention to this), if you select a cell with, say, 98% as its contents (formatted as Percentage, and with the value "hardcoded", not resulting from a formula), what you see in the input field is "98%", not "0.98". `find` may be comparing against this. — sancho.s ReinstateMonicaCellio, Jul 30 '17 at 13:28
@sancho.s Yes, but OP is looking at `Selection.Value`. That value will be 0.98 whether the cell shows 98% or 0.98. So `lookfor` will be 0.98. Range.Find won't find 98% in that situation. To differentiate, he could search for `Selection.Text` — Ron Rosenfeld, Jul 30 '17 at 14:58
@RonRosenfeld - Even if the numeric value stored is 0.98 (no doubt about it, as it can be used in calculations), there seems to be no way for `Find` to look for those numeric values. It appears that `Find` unavoidably looks for values with a given format. You mention how to differentiate both cases. The OP wants exactly the opposite: not to differentiate them. See my answer below. — sancho.s ReinstateMonicaCellio, Aug 08 '17 at 11:02
You can't `.Find()` multiple values in the same line of code. You need to use a second loop or some logical operators to look for more than one value — SierraOscar, Aug 08 '17 at 11:06

Josh Anstead · Answer 1 · 2017-07-30T11:26:20.877

0

Sometimes 98% is the value of the cell. i.e. when you type out 98% in a cell. Excel will treat it as literally 98%. In other cases the cells value is .98 or .98231 and displays 98%. Most likely you want to look for the roundup to two significant digits so that it finds .98 when the value is .98321.

I would try looking for both.

The cell.text and round(cell.value, 2)

How to round up with excel VBA round()?

This article shows how to use the round feature in Excel VBA

edited Jul 30 '17 at 11:26

answered Jul 30 '17 at 10:59

Josh Anstead

328
2
15

thank you, unfortunately the result is not exactly what I am looking for (my question was probably unclear..); I want to find both: 0.98 and 98%.. with your solution, the macro only finds 98% if the active cell value is 98%. – Lennart Jul 30 '17 at 11:09
I hope that explains what you are seeing. – Josh Anstead Jul 30 '17 at 11:21

sancho.s ReinstateMonicaCellio · Answer 2 · 2017-08-08T10:56:19.813

Effectively, Find is missing values with various formats (not only Percentage), even if the numeric value is the same as the source (parameter What of function Find). Under a variety of conditions, listed below, Find only finds cells whose contents is 0.98, either with General or Number (2 decimal digits) format.

What I tried:

Using lookfor = Selection.Value. Changing the number format of the (source) cell pointed at by Selection. It doesn´t matter if the format of Selection is Percentage, Number (with any decimal digits), or General. Find only finds cells with 0.98, and not with 0.980, e.g.
Using lookfor = Selection.Text. Changing the number format of the (source) cell pointed at by Selection. Find only finds cells with the exact same number as viewed.

Strange as it may be, this requires a workaround, as Find would not find both 0.98 and 98% at the same time. One option is to have one or more helper columns with a uniform format, and perform find on these columns.

Ron Rosenfeld · Answer 3 · 2017-08-08T12:09:50.780

When FIND does not work, one solution is to loop through the cells. If you have a large number of cells, this can be slow, so reading the range to be searched into a variant array will improve the speed by ten-fold or more.

But here is an idea with looping that will get you started. Note I used the value2 property. See Charles Williams Answer for the why.

First run the Setup macro, then the findValuePercent, to see how things work. You can then adapt it to your specific requirements.

Option Explicit

Sub Setup()
    Dim WS As Worksheet
Set WS = Worksheets("sheet1")
With WS
    .Cells(1, 1).Value = "98%"
    .Cells(2, 1).Value = 0.98

    Debug.Print .Cells(1, 1).Value, .Cells(1, 1).Text ' --> 0.98    98%
    Debug.Print .Cells(2, 1).Value, .Cells(2, 1).Text ' --> 0.98    0.98
End With
End Sub

Sub findValuePercent()
    Dim WS As Worksheet
    Dim R As Range
    Dim C As Range
    Dim S As String
    Const dWhat As Double = 0.98

Set WS = Worksheets("sheet1")
Set R = WS.UsedRange

For Each C In R
    If C.Value2 = dWhat Then
        S = S & vbLf & C.Address & vbTab & C.Text
    End If
Next C

MsgBox "Matches in" & S

End Sub

VBA Find function ignores percentages -> only finds decimals

3 Answers3