VBA, PLEASE HELP! Code to search for value and if matched return row of information.

[Dremzy]

Hi guys,

I've created a sheet (Sheet 1) which contains rows of information about different countries. On a separate sheet (Sheet 2) I would like to create a search function, where you can type the country code in and the VBA code will search for the value in sheet 1 and then return the row of information if found.

E.G Sheet 2 CELL A5 (THE SEARCH CELL) --> I type in " GBP ", VBA CODE SERACHES FOR GBP in Sheet 1 and returns back the row of information about that country to SHEET 1. ( I would like the row of information to be populated into row D). Each time you search for a different country in CELL A5, Row D1 information is replaced.

Can anyone help with this please?

 

[mumps]

In which column in Sheet1 is the country code located? How many columns of information do you have in Sheet1 and will that ever change? I'm not sure what you mean by

row D

D is a column not a row. Could you please clarify?

 

[sheetspread]

match will do this

 

[Dremzy]

Hi, everyone

I have about 7 columns of information, but if you can provide the fundamental code I am very familiar with tweaking VBA code. I 've just forgotten how to write such a search function.

Apologies, in sheet 2 I want the row of information to return into say Row 4 (Make it whatever row you want). This sheet will be a dedicated search sheet, so you enter what you want to search in cell A5 and the row of information returns into row 4.

Country code is located in column 2 of Sheet 1

thanks for the help

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter your country code in cell A5.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundCode As Range
    Set foundCode = Sheets("Sheet1").Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundCode Is Nothing Then
        foundCode.EntireRow.Copy Sheets("Sheet2").Cells(4, 1)
    End If
    Application.ScreenUpdating = True
End Sub

 

[Dremzy]

Thanks guys

three things as a followo up question:

1) Is there any way to paste the data into the second column on Sheet 2? Instead of column 1. When I changed the (4,1) to (4 , 2) I get an error.
2) Is there a way to setup an error message so if the information isn't found

 

[mumps]

When you copy an entire row, it must be pasted to column 1. Instead of copying the entire row, you can copy the used range in that row and then paste it to whatever column you want. How many columns of data do you have in Sheet1 and will that ever change?

 

[Dremzy]

Hi

There are 5 columns of data. If that changes I have enough understanding to change the code to reflect that.

Also is there a way to perform this function twice within the same macro? There is a third sheet "Sheet 3" that also has data related with the same value (country code) written in Cell A5. I would ilke to copy that back to Row 8.

Thank you for your help, appreciate it.

 

[mumps]

This macro takes into account the number of columns even if they change and also takes care of the third sheet. Make sure that you input the country code in A5 of Sheet3 before you input the country code on A5 of Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim lColumn As Long
    Dim foundCode As Range
    Set foundCode = Sheets("Sheet1").Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundCode Is Nothing Then
        lColumn = Sheets("Sheet1").Cells(foundCode.Row, Columns.Count).End(xlToLeft).Column
        Sheets("Sheet2").Rows(4).EntireRow.ClearContents
        With Sheets("Sheet1")
            .Range(.Cells(foundCode.Row, 1), .Cells(foundCode.Row, lColumn)).Copy Sheets("Sheet2").Cells(4, 2)
        End With
    Else
        Sheets("Sheet2").Rows(4).EntireRow.ClearContents
        MsgBox ("Value in Sheet2, cell A5 not found.")
    End If
    Set foundCode = Sheets("Sheet1").Range("B:B").Find(Sheets("Sheet3").Cells(5, 1), LookIn:=xlValues, lookat:=xlWhole)
    If Not foundCode Is Nothing Then
        lColumn = Sheets("Sheet1").Cells(foundCode.Row, Columns.Count).End(xlToLeft).Column
        Sheets("Sheet3").Rows(8).EntireRow.ClearContents
        With Sheets("Sheet1")
            .Range(.Cells(foundCode.Row, 1), .Cells(foundCode.Row, lColumn)).Copy Sheets("Sheet2").Cells(8, 2)
        End With
    Else
        Sheets("Sheet2").Rows(8).EntireRow.ClearContents
        MsgBox ("Value in Sheet3 cell A5 not found.")
    End If
    Application.ScreenUpdating = True
End Sub