Data Search and hyperlink

[Fiske]

Hi I am writing a macro where the when the user press a commandbutton, the user will input the data he want to find and the macro will search the whole workbook(excluding sheet1) relating to what the user input.
The found cell value will then be copied and paste on sheet1 A1 and which sheet it is from on B1.
The cell A1 also need to be hyperlink thus the user can click the hyperlink and go to that specific sheet and cell to view more data on it.
eg.
[TABLE="width: 500"]
<tbody>[TR]
[TD]food[/TD]
[TD]Sheet5[/TD]
[/TR]
</tbody>[/TABLE]

this is my current code,

Sub myInput()


Dim myValue As String
Dim i As Integer






myValue = InputBox("Enter Search Input")


If myValue = "" Then
MsgBox ("No data found")
End If
Exit Sub
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)


For i = 2 To 4


Sheets(i).Activate
Next i
Cells.Find(What:=myValue, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
If ActiveCell.value = myValue Then
Active.Sheet.ActiveCell.value.Copy Destination:=Sheets(1).cell("A1")


End If


End Sub

The problem i am facing now is the found data wasn't copy to sheet1 and i do not know how to hyperlink and copy the sheetname on to cellB1.
Hope you all can help me solve and find my errors
Thank you

 

[My Aswer Is This]

Try this:

Sub myInput()
'Modified 5/23/18 3:00 AM EDT
Dim myValue As String
Dim i As Integer
Dim c As Range
myValue = InputBox("Enter Search Input")
If myValue = "" Then MsgBox ("No data entered"): Exit Sub
For i = 2 To Sheets.Count
    For Each c In Sheets(i).UsedRange
        If c.Value = myValue Then
            Sheets(1).Cells(1, 2).Value = Sheets(i).Name
            Sheets(1).Hyperlinks.Add Anchor:=Sheets(1).Cells(1, 1), Address:="", SubAddress:= _
            "'" & Sheets(i).Name & "'!" & c.Address, TextToDisplay:=c.Text
            Exit Sub
        End If
    Next
Next
MsgBox "The value  " & myValue & "  Not Found"
End Sub

 

[Fiske]

hi thanks for helping but it only show one searched value on one sheet but did not show the others.
e.g. sheet2 has got "food" and sheet 3 has got "food" but it only show and hyperlink the food for sheet2 and not those on other lines of the same sheet andother sheets

 

[My Aswer Is This]

That's because you said in your original post:

The found cell value will then be copied and paste on sheet1 ​

A1​

and which sheet it is from on ​

B1​

.

So what happens if it finds food three times?
Do the results go in A1 and B1
And A2 and B2
And A3 and B3

??
​

 

[My Aswer Is This]

Try this:

Sub myInput()
'Modified 5/24/18 3:40 AM EDT
Dim myValue As String
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A1:B" & Lastrow).Clear
Dim i As Integer
Dim c As Range
Dim b As Long
myValue = InputBox("Enter Search Input")
If myValue = "" Then MsgBox ("No data entered"): Exit Sub
b = 0
For i = 2 To Sheets.Count
    For Each c In Sheets(i).UsedRange
        If c.Value = myValue Then
        b = b + 1
            Sheets(1).Cells(b, 2).Value = Sheets(i).Name
            Sheets(1).Hyperlinks.Add Anchor:=Sheets(1).Cells(b, 1), Address:="", SubAddress:= _
            "'" & Sheets(i).Name & "'!" & c.Address, TextToDisplay:=c.Text
        
        End If
    Next
Next
If b = 0 Then MsgBox "The value  " & myValue & "  Not Found"
End Sub

 

[Fiske]

yup! sry for my bad interpretation!!

 

[My Aswer Is This]

Did the last script I provided do what you wanted?

 

[Fiske]

Did the last script I provided do what you wanted?

Yup thanks a lot!! ^^

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Yup thanks a lot!! ^^