VBA find function for exact percentages

[Anzek]

Hello,

I have a row of percentages: 100%, 200%, 500%, 1000%, 1500%, etc
I'm trying to find the cell that has 500%. I use the code below. factor is a number in a cell of which value is 500%

Set c = rng.Find(factor, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchFormat:=False)

this doesn't work because lookat:=xlWhole. I tried to concatenate factor with a % sign to find it but didn't work either.
I also tried Application.FindFormat.NumberFormat = "0.00%" and set searchformat:=True and didn't work either.

Please help me. I'm so dizzy and my head hurts. Thanks.

 

[Leith Ross]

Hello Anzek,

You say the code does not work because LookAt:=xlWhole. Is this because the 500% is part of a string?

 

[Fluff]

If your percentages are numbers formatted as percent, then factor should be 5

 

[Anzek]

500% isn't part of a string. I tried 5 too and didn't work. This is part of a custom function. I will post the full code when I'm back at work on Monday.

 

[joeu2004]

I have a row of percentages: 100%, 200%, 500%, 1000%, 1500%, etc
I'm trying to find the cell that has 500%. I use the code below. factor is a number in a cell of which value is 500%
Set c = rng.Find(factor, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchFormat:=False)
this doesn't work

Two cautionary comments:

1. Calculations of percentage is rarely exactly as it appears. The internal representation of calculated numeric values often has more precision than what Excel displays, even with a format that displays as many digits as possible.

For example, =1/3 in A1 might appear to be 33.3333333333333% because Excel formats only the first 15 significant digits (rounded). But Range("A1")=0.333333333333333 is False and Range("A1")=1/3 is True because 1/3 is not exactly 0.333333333333333.

(The exact decimal presentation of the binary representation of 1/3 is 0.333333333333333,314829616256247390992939472198486328125. I use period for the decimal point and comma to demarcate the first 15 significant digits.)


2. The Range.Find method compares the text of cells as they appear, not their numeric value. Consider the following example.

     A       B
1    abc
2    500%    499.9%
3    500%    500%
4    500%    500.1%
5    def
6    500%    '500%
7    xyz

A1:A7 is formatted as Percentage with 0 places. Column B shows what is actually entered into column A, if it is different from appearances.

With A1:A7 selected, the code below displays the following results. Note that the value in A2 is actually 499.9%, but it appears to be 500% due to formatting.

A2=5: False    A3=5: True
5: not found
"500%": $A$2

Sub doit()
Dim c As Range, s As String

s = "A2=5: " & (Range("a2") = 5)
s = s & "    A3=5: " & (Range("a3") = 5)
Debug.Print s
MsgBox s

On Error Resume Next

Set c = Selection.Find(5, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchFormat:=False)
s = "5: "
If c Is Nothing Then s = s & "not found" Else s = s & c.Address
Debug.Print s
MsgBox s

Set c = Selection.Find("500%", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchFormat:=False)
s = Chr(34) & "500%" & Chr(34) & ": "
If c Is Nothing Then s = s & "not found" Else s = s & c.Address
Debug.Print s
MsgBox s
End Sub

 

[joeu2004]

You might consider the following implementation instead:

Sub doit()
Dim r As Long
On Error Resume Next
r = WorksheetFunction.Match(5, Selection, 0)
If Err <> 0 Then
    MsgBox "500% not found"
    Exit Sub
End If
MsgBox "500%: " & Selection(r).Address
End Sub

But keep in mind my previous cautionary comment #1 (exact binary value). Consider the following data:

     A       B                C        D
1    abc
2    500%    499.9%
3    500%    =500% + 9E-16    FALSE    =A3-5=0
4    500%    500%             TRUE     =A4-5=0
5    500%    500.1%
6    def
7    500%    '500%
8    xyz

A1:A8 is formatted as Percentage with 0 places. Column B shows what is actually entered into column A, if it is different from appearances. Column D shows the formulas in column C.

With A1:A8 selected, the code above finds A4, not A3, because A3 is not exactly 500% despite appearances due Excel's arbitrary formatting limitations.

(The decimal representation of the exact value in A3 is 5.00000000000000,088817841970012523233890533447265625. I use period for the decimal point and comma to demarcate the first 15 significant digits.)

The only way that I can think of off-hand to work around the binary anomalies is a loop:

Sub doit2()
Dim c As Range
For Each c In Selection
    If WorksheetFunction.IsNumber(c) Then
        If WorksheetFunction.Round(c.Value2, 15) = 5 Then Exit For
    End If
Next c
If c Is Nothing Then MsgBox "500% not found" _
Else MsgBox "500%: " & c.Address
End Sub

 

[Leith Ross]

Hello Anzek,

In cases with large data sets, I prefer to let the system do some filtering for me before the search begins. This macro uses the SearchFormat argument in the Find method to let the system filter the cells we are interested in.

Sub FindByFormat()


    Dim Addx    As String
    Dim Cell    As Range
    Dim NF      As String   ' Number Format
    Dim ret     As Integer
    Dim Rng     As Range
    Dim Wks     As Worksheet
    Dim What    As Variant  ' Search value
    
        NF = "0%"
        What = "500%"
        
        Set Wks = ActiveSheet
        Set Rng = Wks.UsedRange
        
        With Application.FindFormat
            .Clear
            .NumberFormat = NF
        End With
        
        Set Cell = Rng.Find(What, , xlValues, xlWhole, xlByRows, xlPrevious, False, False, True)
        
        If Cell Is Nothing Then Exit Sub Else Addx = Cell.Address
        
        Do
            ret = MsgBox("Found  at " & Cell.Address & vbLf & "Continue?", vbYesNo)
            If ret = vbNo Then Exit Do
            Set Cell = Rng.Find("*", Cell, xlValues, xlWhole, xlByRows, xlNext, False, False, True)
            If Cell.Address = Addx Then Exit Do
        Loop
        
        Application.FindFormat.Clear
        
End Sub

 

[joeu2004]

Oops....

You might consider the following implementation instead:
[....]
r = WorksheetFunction.Match(5, Selection, 0)
[....]

     A       B                C        D
[....]
3    500%    =500% + 9E-16    FALSE    =A3-5=0
4    500%    500%             TRUE     =A4-5=0
[....]

[....]
With A1:A8 selected, the code above finds A4, not A3, because A3 is not exactly 500% despite appearances due Excel's arbitrary formatting limitations.

Poor example because, in that case, you probably would prefer A4 instead of A3 anyway.

But what if A4 did not exist? That is, if there is no cell whose binary value is exactly the binary representation of 500%?

I think you would expect A3 to be matched because it appears to be 500% when formatted to display 15 significant digits.

That would be the case with the range.Find method.

But as I noted before, the range.Find method might choose an unintended cell (e.g. 499.9%) simply because the cell format displays too little precision.

The point is: there is no single solution that works in all cases. You need to think about your situation and your goals, and choose the appropriate solution that meets them.

 

[Anzek]

Application.FindFormat.NumberFormat = "0.00%"
Set c = lookuprange.Cells(1, Application.WorksheetFunction.Match(factor, range(lookuprange.Cells(1, 1), lookuprange.Cells(1, 1).Offset(0, lookuprange.Columns.Count)), 0))
If Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Match(factor, range( lookuprange.Cells(1, 1),  lookuprange.Cells(1, 1).Offset(0,  lookuprange.Columns.Count)), 0)) Then
column number = c.column
Else
For Each cell In newrange.Cells
do stuff
next cell

Above is the code. I decided to do everything with excel functions instead, but thanks for all the suggestions.