How to find “single cell result”of a relative defined name in a VBA code?

soroush.kalantari · #1 07-22-2023, 04:46 AM

I am seeking a way to find the “single cell result” of relative defined names in VBA codes. (see the excel attachment in which the name “price” has been defined. (for example, if select G3 in a vba code and somehow separate the “price” in its formula, I am seeking a way to get “G1” which is the “single cell result” of price name in this cell. as the picture attachment shows, it can be achieved manually by formula/evaluate formula/step in/evaluate.)

Can you guide me on this issue?

Logit · #2 07-22-2023, 07:31 PM

Attached are three examples that can be molded to your needs.

Code:

Option Explicit

Sub FindInLists()
     '
     '
     '
     
    Dim SheetsToSearch, SrchStrg As String, ws As Excel.Worksheet, r As Range
     Set ws = Sheets("Sheet1")
    SrchStrg = Application.InputBox("Enter Term to search ", "Search Term", Type:=2)
    
    With ws
        
            With ws.Range("A2:AB5000")  'EDIT RANGE AS REQUIRED
                Set r = .Find(what:=SrchStrg, After:=.Range("A1"))   'find the cell whose value is equal to SrchStrg and activate it
                If Not r Is Nothing Then
                    ws.Activate: r.Activate
                    ws.Range("B1").Value = SrchStrg
                    ws.Range("D1").Value = ActiveCell.Address
                                       
                ElseIf r Is Nothing Then
                    MsgBox "Search term does not exist. ", vbInformation, "Item Not Found"
                End If
            End With
        
    End With
     
End Sub

soroush.kalantari · #3 07-23-2023, 08:13 PM

Quote:

Originally Posted by Logit

Attached are three examples that can be molded to your needs.

Code:

Option Explicit

Sub FindInLists()
     '
     '
     '
     
    Dim SheetsToSearch, SrchStrg As String, ws As Excel.Worksheet, r As Range
     Set ws = Sheets("Sheet1")
    SrchStrg = Application.InputBox("Enter Term to search ", "Search Term", Type:=2)
    
    With ws
        
            With ws.Range("A2:AB5000")  'EDIT RANGE AS REQUIRED
                Set r = .Find(what:=SrchStrg, After:=.Range("A1"))   'find the cell whose value is equal to SrchStrg and activate it
                If Not r Is Nothing Then
                    ws.Activate: r.Activate
                    ws.Range("B1").Value = SrchStrg
                    ws.Range("D1").Value = ActiveCell.Address
                                       
                ElseIf r Is Nothing Then
                    MsgBox "Search term does not exist. ", vbInformation, "Item Not Found"
                End If
            End With
        
    End With
     
End Sub

Thank you for your reply.
I think I should clarify my issue more.
Take the following example code.
Sub getsinglecellresult()
Dim t As String
'separate " the relative defined name. When running on g3, the result is "price" which is the relative defined name. (I know it is not an optimum way for separating price but I have some idea to do it by a UDF function and it is not my problem now)

t = Mid(Right(Selection.Formula, Len(Selection.Formula) - 1), 1, 5)

'Now, I am seeking some lines of code which by utilizing t(price) , give me its "singel cell result" which is G1 (when running the code on G3).
End Sub

Logit · #4 07-24-2023, 09:39 AM

G1 displays a formula ( =F1+10 ) that creates a result of : 100 .

Are you wanting to obtain the formula or the value 100 ?

soroush.kalantari · #5 07-24-2023, 07:50 PM

Quote:

Originally Posted by Logit

G1 displays a formula ( =F1+10 ) that creates a result of : 100 .

Are you wanting to obtain the formula or the value 100 ?

I am wanting to obtain the cell address($G$1 or G1)

Logit · #6 07-24-2023, 09:10 PM

Where did you derive the vba formula :

t = Mid(Right(Selection.Formula, Len(Selection.Formula) - 1), 1, 5)

soroush.kalantari · #7 07-25-2023, 07:52 PM

Quote:

Originally Posted by Logit

Where did you derive the vba formula :

t = Mid(Right(Selection.Formula, Len(Selection.Formula) - 1), 1, 5)

I have written this formula simply such that to get “price” from it. As I mentioned, my question is not: how to separate a relative name from a formula? but it is: given a relative name of an active cell formula in a vba code, is there some line of code to get “singe cell result address” of this relative name( here the price)?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
referencing relative cell	trevorc	Excel Programming	2	08-12-2019 11:56 AM
Relative link (same folder) from Excel cell into Word file	SparFuX	Word	1	12-18-2018 03:22 PM
One Cell that controlls spread sheet result button to change simple fomula result	RAH	Excel Programming	5	03-31-2018 04:52 PM
Sumif to find a single word in a cell without using wildcard	kiteman	Excel	3	08-23-2017 10:14 AM
Worksheet defined cell sums	flextera	Excel	3	06-17-2014 11:12 AM