SEARCH MACRO FOR VALUE IN WORKBOOK, RETURN value of adjacent cell.

[thequiff]

I need to a search from "sheet1" I would like to be able to enter a search term that would be a sku number (16 digits) It needs to look in every sheet but only in Column H.

When it finds the item I need it to return the result in Column H.


For example.

Search MA00HH12345BU1FB

finds the result on sheet5 in cell B7

Now show the result as cell H7

I would need a reset button for the search field and result field too?

Many Thanks

Matt

[rkey]

In you example your find the result in cell B7, in you explaination you only search in column H of every sheet.
Can you explain a bit more (perhaps an example file).

[thequiff]

I've added an attachment

So on Sheet1 I would like to make C3 a search field
and C5 a result field.

If you search,ie GT55NW02332SV1SV it will find it in sheet 2 Cell B9
(it will also find matches where the search term will match hence it only needs to search column B of every sheet).

The return information needs to come from cell H9 this will then show the location on the network.

The sample sheet is only rough but the principle is there.

[rkey]

Is this what you want?

SampleSearch.xlsm

[thequiff]

Indeed indeed!!

Many thanks
you made that too easy!

[thequiff]

Can you see how I formed the link?

Just wondering if you know how I can make i stay a hyperlink?

[rkey]

Try this:

Sub search()

For Each WS In Worksheets
    If WS.Name <> "Sheet1" Then
        With WS.Range("B:B")
            Set Rng = .Find(What:=Worksheets("Sheet1").Range("C3").Value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Worksheets("Sheet1").Range("C5").Value = Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
                Worksheets("Sheet1").Range("C5").Hyperlinks.Add Anchor:=Selection, Address:= _
                    Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value _
                , TextToDisplay:= _
                    Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
            Else
                
            End If
        End With
    End If
Next WS

End Sub

[thequiff]

I've modified it a little to work with my Spreadsheet, however In C34 it shows the link as TEXT ONLY. but in B38 a hyperlink (now highlighted in Blue)
So its almost working but can't understand why its not in the right place as there is no indication of B38 in code.

Sub Search_by_Sku()
'
' Search_by_Sku Macro
'

'


For Each WS In Worksheets
If WS.Name <> "HOME PAGE" Then
With WS.Range("B:B")
Set Rng = .Find(What:=Worksheets("HOME PAGE").Range("C31").Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Worksheets("HOME PAGE").Range("C34").Value = Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
Worksheets("HOME PAGE").Range("C34").Hyperlinks.Add Anchor:=Selection, Address:= _
Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value _
, TextToDisplay:= _
Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
Else

End If
End With
End If
Next WS

End Sub

[rkey]

Hyperlink lands on current active cell, although it is pointed at C34...(dunno why?)
It can be solved by 1st selecting the required cell.

Sub Search_by_Sku()
'
' Search_by_Sku Macro
'

'


For Each WS In Worksheets
If WS.Name <> "HOME PAGE" Then
With WS.Range("B:B")
Set Rng = .Find(What:=Worksheets("HOME PAGE").Range("C31").Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Worksheets("HOME PAGE").Range("C34").Value = Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
Worksheets("HOME PAGE").Range("C34").Select
Worksheets("HOME PAGE").Range("C34").Hyperlinks.Add Anchor:=Selection, Address:= _
Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value _
, TextToDisplay:= _
Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
End With
End If
Next WS

End Sub

[thequiff]

Hi I'm still trying to work out the best script for this. I've been trying to populate a fill down.

But I had another Idea that on the search result it should return the answer by doing an equation


at the moment it returns the value of H as hyperlink
So if the sku is found at B67 then the equation would look like this



=HYPERLINK(CONCATENATE("file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week",E67,"\",P1,"_WK",E67,"_LR\",B67,".jpg"))

E67 and B67 are the 2 numbers that would change and P1 would always be P1


Hope that make sense to what I am trying to achieve!


Many Thanks

Matt

[rkey]

It makes sense :P
I actually never use Concatenate in function, i got a habit of using the ampersand "&".
So with your example it would turn into this:

=HYPERLINK("file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week"&E67&"\"&P1&"_WK"&E67&"_LR\"&B67&".jpg")

[thequiff]

How would I add it to the macro code though? thanks for the & hint though!

I assume somewhere in here but not sure how to add it in? I want to replace the H line?

Worksheets("HOME PAGE").Range("C34").Value = Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
Worksheets("HOME PAGE").Range("C34").Select
Worksheets("HOME PAGE").Range("C34").Hyperlinks.Add Anchor:=Selection, Address:= _
Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value _
, TextToDisplay:= _
Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value

[rkey]

Yes, somewhere in there, just after Adress:=

You can replace this:

Worksheets("HOME PAGE").Range("C34").Value = Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
Worksheets("HOME PAGE").Range("C34").Select
Worksheets("HOME PAGE").Range("C34").Hyperlinks.Add Anchor:=Selection, Address:= _
Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value _
, TextToDisplay:= _
Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value

to this:

Worksheets("HOME PAGE").Range("C34").Value = Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
Worksheets("HOME PAGE").Range("C34").Select
Worksheets("HOME PAGE").Range("C34").Hyperlinks.Add Anchor:=Selection, Address:= _
                    "file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week" & _
                    Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "\" & _
                    Worksheets(Rng.Worksheet.Name).Range("P1").Value & "_WK" & _
                    Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "_LR\" & _
                    Worksheets(Rng.Worksheet.Name).Range("B" & Rng.Row).Value & ".jpg" _
                    , TextToDisplay:= _
                    Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value

[thequiff]

Hi many Thanks for getting back, i'm still getting run time errors when I try to use the search facility.

I get a run time error'5'
Invalid procedure or argument.

It then highlights the exact code you just mentioned.

On a separate note What happens if it can't find anything? I would need to put a comment saying none found in C34?
I've uploaded it here, Submissions.xlsm


Many thanks for your time, I hope this will save me time in the future!

Matt

[rkey]

It is on the TextToDisplay:=
You can change it to this:

Sub Search_by_Sku()
'
' Search_by_Sku Macro
'

'


For Each ws In Worksheets
    If ws.Name <> "HOME PAGE" Then
        With ws.Range("B:B")
            Set Rng = .Find(What:=Worksheets("HOME PAGE").Range("C31").Value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Worksheets("HOME PAGE").Range("C34").Value = Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
Worksheets("HOME PAGE").Range("C34").Select
Worksheets("HOME PAGE").Range("C34").Hyperlinks.Add Anchor:=Selection, Address:= _
                   "file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week" & _
                   Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "\" & _
                   Worksheets(Rng.Worksheet.Name).Range("P1").Value & "_WK" & _
                   Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "_LR\" & _
                   Worksheets(Rng.Worksheet.Name).Range("B" & Rng.Row).Value & ".jpg" _
                   , TextToDisplay:= _
                   "file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week" & _
                   Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "\" & _
                   Worksheets(Rng.Worksheet.Name).Range("P1").Value & "_WK" & _
                   Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "_LR\" & _
                   Worksheets(Rng.Worksheet.Name).Range("B" & Rng.Row).Value & ".jpg"
            Else
                
            End If
        End With
    End If
Next ws

End Sub

[thequiff]

Almost working it now comes up with
Method 'List' of object 'CommandBarComboBox' failed

but does display the link!

Just searching the web to find out what it means.

[rkey]

It is cause by the Workbook_SheetChange on this row:
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
Every action of the sub Search_by_Sku will trigger this event.

You can get around it to start with an screenupdate False en end with screenupdate True.
Try this:

Sub Search_by_Sku()
'
' Search_by_Sku Macro
'

'

Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name <> "HOME PAGE" Then
        With ws.Range("B:B")
            Set Rng = .Find(What:=Worksheets("HOME PAGE").Range("C31").Value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Worksheets("HOME PAGE").Range("C34").Value = Worksheets(Rng.Worksheet.Name).Range("H" & Rng.Row).Value
Worksheets("HOME PAGE").Range("C34").Select
Worksheets("HOME PAGE").Range("C34").Hyperlinks.Add Anchor:=Selection, Address:= _
                   "file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week" & _
                   Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "\" & _
                   Worksheets(Rng.Worksheet.Name).Range("P1").Value & "_WK" & _
                   Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "_LR\" & _
                   Worksheets(Rng.Worksheet.Name).Range("B" & Rng.Row).Value & ".jpg" _
                   , TextToDisplay:= _
                   "file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week" & _
                   Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "\" & _
                   Worksheets(Rng.Worksheet.Name).Range("P1").Value & "_WK" & _
                   Worksheets(Rng.Worksheet.Name).Range("E" & Rng.Row).Value & "_LR\" & _
                   Worksheets(Rng.Worksheet.Name).Range("B" & Rng.Row).Value & ".jpg"
            Else
                
            End If
        End With
    End If
Next ws
Application.ScreenUpdating = True
End Sub

[thequiff]

Fingers crossed it all seams to be running ok. I'm sure i'll be back.

Many Many Thanks

Matt