Search Value on Multiple Excel Spreadsheet

[didzmad]

Hello,
I have been trying over the past couple of weeks to find some piece of VBA code that I could use to do this simple function :
I have a work book with multiple sheets. I have created a button on each sheet in A1 upon clicking opens a vba form box with an input box and a search and close button. Upon entering a reference number in the input box and clicking search I want the VBA code to search for this exact match value on each worksheet (line 2 only) and once found go and select the cell with offset (0, 5) and Macro can end if value not found then a msg box needs to come up saying "not found" .

on each sheets I have the following table structre

A B C D E F G H
1 TYPE FDH TNW TNW GAH TNW FDH FDH
2 REF 533 666 345 567 322 634 654
3 DATE data data data data data data data
4 COLOUR data data data data data data data
5 PRICE data data data data data data data
6 QTY data data data data data data data
7 VAT data data data data data data data
8 OFFER data data data data data data data

If Someone enters 345 in Input box the vba formula should take then to "D7" which is the cell offset by 5 from where the value was found in "D2"

hope this makes sense and I have made it clear enough. I have only subscribed to this site. I should have done it earlier as posts on here have always been very helpful.

 

[mumps]

Try:

Sub FindVal()
    Application.ScreenUpdating = False
    Dim response As String, val As Range
    response = InputBox("Please enter a value.")
    If response = "" Then Exit Sub
    Set val = Rows(2).Find(response, LookIn:=xlValues, lookat:=xlWhole)
    If Not val Is Nothing Then
        val.Offset(5, 0).Select
    Else
        MsgBox (response & " Not found.")
    End If
    Application.ScreenUpdating = True
End Sub

 

[didzmad]

thank you Mumps. I have just updated my code and it keeps telling me Value Not Found although the value is in one of Rows(2) cell. I will look at your code a little more closely later when I finish work.

 

[mumps]

When I tried the macro on a dummy sheet it worked properly. Please let me know how it works out.

 

[didzmad]

OK got it working on one sheet. But when Ref is located on a different sheet it does not find it. The macro needs to be able to search on all the Sheets within that workbook

 

[mumps]

Try:

Sub FindVal()
    Application.ScreenUpdating = False
    Dim response As String, val As Range, ws As Worksheet
    response = InputBox("Please enter a value.")
    If response = "" Then Exit Sub
    For Each ws In Sheets
        Set val = ws.Rows(2).Find(response, LookIn:=xlValues, lookat:=xlWhole)
        If Not val Is Nothing Then
            ws.Activate
            val.Offset(5, 0).Select
        Else
            MsgBox (response & " not found in sheet " & ws.Name)
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Please note that in order for the macro to select the 'offset' cell, it has to first activate the sheet. If you are doing something else other than selecting the cell such as copying it, the sheet doesn't have to be activated.

 

[didzmad]

Thanks for your help. It looks perfect . I will incorporate to what I have and let you if of any issues.

 

[mumps]

You are very welcome.

 

[didzmad]

Hey . thanks for your help . I have modified to code a bit to suit my needs and works perfect. Thank you.

 

[mumps]

My pleasure.