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

Dremzy

New Member
Joined
Apr 19, 2014
Messages
29
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
D is a column not a row. Could you please clarify?
 
Upvote 0
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
 
Upvote 0
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.
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top