PDA

View Full Version : Solved: Need VBA Code to search one WS from another WS



kabbak
01-08-2012, 02:29 AM
I would like to search data in one WS (Infosheet) from another WS (GUI).
I would like to enter a name(to be searched) in some cell and press a macro button which should give the whole row from the infosheet on which the searched name appears (the search should display, on the GUI sheet, the entire row from the infosheet).
It should be able to give atleast 5 instances on subsequent rows since some entries appear more than once.

In the attached file, i had managed to make the code(through macro) to insert entry details from the GUI WS. plus another to sort the database on the infosheet by due date. Most cells on the GUI sheet are locked but there's no password to unlock.

Other modifications too(not too advanced though) are welcome.

Its for some loan biz that i intend to start.

THANKS IN ADVANCE.

mdmackillop
01-08-2012, 10:21 AM
Option Explicit

Sub GetData()
Dim Nm As String
Dim wsSource As Worksheet
Dim wsTgt As Worksheet
Dim c As Range
Dim FirstAddress As String

Set wsSource = Sheets("Info Sheet")
Set wsTgt = Sheets("GUI")

Nm = wsTgt.Range("F6")

wsTgt.Cells(25, 1) = "Data"
With wsSource.Range("C:C")
Set c = .Find(Nm, LookAt:=xlWhole)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.EntireRow.Copy wsTgt.Cells(Rows.Count, 1).End(xlUp)(2)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub

kabbak
01-09-2012, 12:47 AM
Thanx you sir mdmckillop. I studied and interpreted the code and knew what was required of me. It worked but i still need further help.
As seen in the modified attached 2nd file on this reply, i have changed the gui sheet layout and specified where exactly i would like and which entries in particular from the info sheet i'd like to see after searching.
I do have formulas there and had maneuvered locally via excel functions. With mine, entering a name under Search "Name" automatically populates the rest of the details. Try through but Don't Laugh. Its limitation is that i have to enter the names differently incase of multiple entries coz my cheap formulas cannot search multiple entries.
Can/May you pliz provide some code to work with the modified file and with the name to be searched entered under Name as tabled! I just dont know how multiple entries will be handled, but am confident that you'll crash the challenge this time too.
Can you pliz give some code to lock the scroll of the GUI sheet to (A1:O34). Its true there are codes on this site to do that but i dont know what am doing wrong. They've failed to work. Could be because am a self-taught Newbie.

mdmackillop
01-09-2012, 11:31 AM
Option Explicit

Sub GetData()
Dim Nm As String
Dim wsSource As Worksheet
Dim wsTgt As Worksheet
Dim c As Range
Dim FirstAddress As String
Dim arr
Dim Rw As Long


Set wsSource = Sheets("Info Sheet")
Set wsTgt = Sheets("GUI")

Nm = wsTgt.Range("F6")

Rw = 23
With wsSource.Range("C:C")
Set c = .Find(Nm, LookAt:=xlWhole, after:=.Cells(1, 1))
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Rw = Rw + 2
arr = c.Offset(, -2).Resize(, 16).Value
Cells(Rw, 4) = arr(1, 3) 'Name
Cells(Rw, 5) = arr(1, 5) 'Loan Amount
Cells(Rw, 6) = arr(1, 7) 'Months
Cells(Rw, 7) = arr(1, 8) 'Paid
'etc

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub


and



Worksheets("GUI").ScrollArea = "A1:O34"

kabbak
01-11-2012, 04:08 AM
Thanks mdmackillop. :bow: You are the Man. :thumb