|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to find “single cell result”of a relative defined name in a VBA code?
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Quote:
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 Last edited by soroush.kalantari; 07-23-2023 at 11:43 PM. |
#4
|
|||
|
|||
G1 displays a formula ( =F1+10 ) that creates a result of : 100 .
Are you wanting to obtain the formula or the value 100 ? |
#5
|
|||
|
|||
I am wanting to obtain the cell address($G$1 or G1)
|
#6
|
|||
|
|||
Where did you derive the vba formula :
t = Mid(Right(Selection.Formula, Len(Selection.Formula) - 1), 1, 5) |
#7
|
|||
|
|||
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)?
|
Tags |
evaluate, relative defined names |
|
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 |