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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,144
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?
 

Dremzy

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

Well-known Member
Joined
Apr 11, 2012
Messages
10,144

ADVERTISEMENT

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:

Dremzy

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

Well-known Member
Joined
Apr 11, 2012
Messages
10,144

ADVERTISEMENT

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

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

Well-known Member
Joined
Apr 11, 2012
Messages
10,144
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,921
Messages
5,598,896
Members
414,267
Latest member
chemhany

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
Top