Need to Recall the data from another Sheet without using VLOOKUP or User Form

anuwars

New Member
Joined
Oct 8, 2014
Messages
8
Dear Friends,

I am new and first time into this forum. I am working on a small work book for our office. Kindly help me to complete the same.

In sheet1 - I have selected some cells to key in data ( eg. A4, B6,B9, C10,C14). This data has been copied to Sheet2 in a row with correct serial as ( A2,B2,C2,D2,E2,).

Note: A1,B1,C1,D1,E1 is carrying the title of the row.
Sheet1 data is copied to Sheet2 with the help of a macro vb code.
Down rows of Sheet2 also copied whenever the records entered in sheet1
.
Now what I need is,
In the sheet1 - On the cell A1, if i write the lookup values for sheet2 rows, i want to display back all those cells which is used initially.
Note: I don't want to use the vlookup formula
I don't want to create a user form in the excel

Simply when i write A1 and enter(or) using any button, my related data to be displayed back in A4,B6,B9,C10,C14)

Hope i have clarified my requirement. I need help from you my friends. Hope you will help me.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Hello & welcome
Is this what your after?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Rw As Long

    If Not Target.Address = "$A$1" Then Exit Sub

Application.EnableEvents = False

    Rw = WorksheetFunction.Match(Range("A1").Value, Sheets("Sheet2").Range("A:A"), 0)
    With Sheets("Sheet2")
        Range("A4") = .Range("A" & Rw)
        Range("B6") = .Range("B" & Rw)
        Range("B9") = .Range("C" & Rw)
        Range("C10") = .Range("D" & Rw)
        Range("C14") = .Range("E" & Rw)
    End With

Application.EnableEvents = True

End Sub
This needs to go in the sheet module.
Right Click on the sheet tab in Excell, select View Code & paste the above code in the window
 

anuwars

New Member
Joined
Oct 8, 2014
Messages
8
Thanks a lot Fluff. Super. It was perfectly working, But i have a problem while applying this code.
.
When i write the correct record in A1 - Data displayed in respective cells.
When I write the wrong record in A1 - It is going out of the excel and showing error
.
Agan When i try with correct record in A1 - It is not working.
.
Please show me an alert stating " No Records in Sheet2" incase of wrong record in A1 instead getting out in excel
.
thanks in advance
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Give this a go
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'anuwars
    Dim Rw As Long

    If Not Target.Address = "$A$1" Then Exit Sub

Application.EnableEvents = False

    On Error Resume Next
    Rw = WorksheetFunction.Match(Range("A1").Value, Sheets("Sheet2").Range("A:A"), 0)
    On Error GoTo 0
    If Rw = 0 Then GoTo xit

    With Sheets("Sheet2")
        Range("A4") = .Range("A" & Rw)
        Range("B6") = .Range("B" & Rw)
        Range("B9") = .Range("C" & Rw)
        Range("C10") = .Range("D" & Rw)
        Range("C14") = .Range("E" & Rw)
    End With

Application.EnableEvents = True

Exit Sub

xit:
    MsgBox "No Records in Sheet 2"
Application.EnableEvents = True

End Sub
 

anuwars

New Member
Joined
Oct 8, 2014
Messages
8

ADVERTISEMENT

Thank you my friend. I will test and let u know.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Not working.. Please guide further
In what way is not working? Any error messages? If so what & where?
 

anuwars

New Member
Joined
Oct 8, 2014
Messages
8
Dear Fluff.

I am sorry. It is working fine now. If any problem, i will let you know.
Thanks for your guidance.

Thanks
 

anuwars

New Member
Joined
Oct 8, 2014
Messages
8
Dear fluff, when I am trying the code in 2007 excel. It is working fine. But not working EXCEL 2013. Please help as our office changed TO MSOFFICE 2013. Expecting your reply.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,959
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top