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

Logit · #1 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 · #2 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

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