VBA find function for exact percentages

Anzek

New Member
Joined
Jan 20, 2016
Messages
17
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello Anzek,

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
If your percentages are numbers formatted as percent, then factor should be 5
 

Anzek

New Member
Joined
Jan 20, 2016
Messages
17
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

Well-known Member
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.

Rich (BB code):
     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.

Rich (BB code):
A2=5: False    A3=5: True
5: not found
"500%": $A$2

Rich (BB code):
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
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
You might consider the following implementation instead:

Rich (BB code):
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:

Rich (BB code):
     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:

Rich (BB code):
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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.

Code:
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

Well-known Member
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
Oops....
You might consider the following implementation instead:
[....]
r = WorksheetFunction.Match(5, Selection, 0)
[....]
Code:
     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.
 
Last edited:

Anzek

New Member
Joined
Jan 20, 2016
Messages
17
Code:
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.
 
Last edited:

Forum statistics

Threads
1,136,327
Messages
5,675,128
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top