Microsoft Office Forums - View Single Post - How to find “single cell result”of a relative defined name in a VBA code?

soroush.kalantari · 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