Find function to locate result of a formula in a cell

0 votes

In order to return the cell reference, I'm attempting to search across column F for the number 9999.

Only when the number 9999 is entered into column F does my code provide the cell where this number is situated.

Formula-driven column F yields the result 9999 as the result of the formula's reasoning.

Dim foundRng as Range
Set foundRng = Range ("F1:F2000").Find("9999")
If foundRng is Nothing then
    MsgBox "Value not found"
Else
    MsgBox foundrng.Address
End if
End Sub
Nov 17, 2022 in Others by Kithuzzz
• 38,010 points
246 views

1 answer to this question.

0 votes

There are several attributes to the Find function. The majority of these are automatically set by Excel based on the most recent use. Excel will remember your most recent option even if you last used Find manually. Therefore, while using VBA, all pertinent properties should be set.

Sub FindCell()

    Dim foundRng As Range
    
    Set foundRng = Range("F1:F2000").Find(What:="9999", _
                                          After:=Cells(2000, "F"), _
                                          LookIn:=vbValues, _
                                          LookAt:=xlWhole, _
                                          SearchOrder:=xlByRows, _
                                          SearchDirection:=xlNext, _
                                          MatchCase:=False)
    If foundRng Is Nothing Then
        MsgBox "Value not found"
    Else
        MsgBox foundRng.Address
    End If

The LookIn property, which I believe your system currently uses xlFormulas (it's xlValuesabove), is the most likely cause of your issue. The formula and value of a cell are the same when it contains a hard value, such as 9999. However, if the formula is =3*3333, the value and the formula diverge, and the number 9999 is not found in the formula.

answered Nov 17, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,420 points
364 views
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
775 views
0 votes
0 answers

What is the formula to keep first two words in a cell over excel?

I want to maintain the first two ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,010 points
196 views
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
511 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
835 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,134 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
431 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
693 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,420 points
347 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
2,978 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP