Find a cell value from range and cell name

tim91700

New member
Local time
Today, 11:04
Joined
Jul 27, 2013
Messages
5
Hello,

Take an excel document, name the cell H3 as "test" (without " ")

Put "tes" (without " ") in the cell F3 as a cell value

The aim of the sub, is to take the F3.Value, and the range H1:H100, and find the value of the cell H3.

I find a code to find the value, but not with the cell name information (the one in F3) :
Code:
Sub Bouton1_Cliquer()
Dim FindString As String
    Dim Rng As Range
    FindString = InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
        With Sheets("Feuil1").Range("H:H")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

Can you help me to find the correct way to do it ?

Thanks you
 
name the cell H3 as "test" (without " ")

I think you mean type test in H3, not name the Cell, if I am wrong apologies and I currently cannot help, but if I am correct then,

Change this

Code:
FindString = InputBox("Enter a Search value")

to this

Code:
FindString = Sheets("Feuill").Range("F3")

and if "tes" was not a typo, i.e. you want to do a partial search then change

Code:
LookAt:=xlWhole,

to

Code:
LookAt:=xlPart,

Brian
 
Hello,

Thanks for details, but the H3 name need to be "test", his value is equal to a number of line, like 100.
The aim is to dispatch some informations from another sheet, iincrement this number, and do something.
 
OK, in that case I think that you want something like this

Code:
Sub getvalue()
Dim r As Range
Dim v As Long
Dim n As String

n = Sheets("sheet1").Range("F3").Value

Set r = ThisWorkbook.Names(n).RefersToRange
v = r.Value
Debug.Print v
End Sub

I tested this after some research and referring to

www.cpearson.com/excel/DefinedNames.aspx

Brian
 
Thanks, that exactly what i'm looking for. But the name need to be equal to the value

Now, if h3.name is test, f3.value is tes, I want to find the value of h3.

I mean, I want to work with approx name, like begin and end by.
I'm sure about the value of F3 but not the H3.name.

Because there is some "_" or "-" or " " in the h3.name

Thanks you for your help
 
I don't know if there is any way to search for a defined name on part of it.
You can return all of the names in a workbook, I think it tells you how in help, and maybe you can do a test against them and go from there, I haven't got time to look at that today.

Brian
 
Thanks, I know how to find a sheet name with partial informations.

Your idea is to list the h:h.cellname in a tab, and check is the F3.value exist.
If yes, I get the value of the cell.

Here is a part of the solution :
Code:
Sub Test()
    Dim r As Range
    Dim cell As Range
    Dim n As String
    
    n = Sheets("Feuil1").Range("F3").Value
    Set r = Sheets("Feuil1").Range("H1:H200")
    If r.Cells.Count > 1 Then
        For Each cell In r.Cells
            If cell.Name Islike n Then
                Sheets("Feuil1").Range("D3").Value = cell.Value
            End If
        Next cell
    End If
End Sub

Can you correct me, because I don't know the exact syntax for the operator Islike.

Thanks you
 
Last edited:
Try

If cell.name like "*" & n & "*" then

Brian

Ps I am assuming cell.name is valid as I have never used it and cannot check at the moment.
 

Users who are viewing this thread

Back
Top Bottom