Forum Home
Press F1
 
Thread ID: 40978 2003-12-25 23:01:00 Excel Macro to search a database returning matches straka01 (310) Press F1
Post ID Timestamp Content User
203131 2003-12-25 23:01:00 I've been trying to write a macro that will search through a list of monthly invoice details (recorded as a list in an excel sheet) and return the matching data to the statement based on the unique customer numbers - but I can't quite get it to work properly.
This is what I want it to do:
Firstly, I input the unique customer number in the statement and start a macro that will search for all the occasions that this customer number occurs in the saved list. Whenever it finds a match it copies the row of data from the saved invoices database into the statement - adding it to the next row down.
I've been trying to use the IF,THEN statements but my knowledge is limited and I don't really want to spend a lot of time learning the intricacies of Excel Macros...
If anyone could just create some code that could point me in the right direction I'd be grateful. Thanks.
P.S. Below is a sample of the database I need to search...

Date Inv.Nbr. Customer Ord.Nbr. Subtotal Freight TOTAL
7/06/03 55555 M1450 berty $1,156.00 $21.00 $1,554.00
1/07/03 2996 M1462 234 $19.29 $3.50 $25.20
1/07/03 Various S3511 1222 $524.86 $0.00 $590.46
1/07/03 2997 M1451 987 $31.02 $4.50 $39.40
1/07/03 3105 M1604 joan $62.22 $0.00 $70.00
1/07/03 3001 M1445 52 $125.33 $0.00 $141.00
1/07/03 3003 M1450 126 $16.80 $4.50 $23.40
2/07/03 3002 M1581 733 $54.10 $3.50 $64.36
7/07/03 3007 M1549 Mike $249.77 $0.00 $280.99
7/07/03 3006 M1557 9678 $75.20 $3.50 $88.10
8/07/03 3008 M1450 Koru $76.00 $3.50 $89.00
8/07/03 3009 M1457 TradeMe $182.22 $5.35 $210.35
8/07/03 3010 C118 john $448.42 $4.50 $508.97
10/07/03 3011 M1475 223 $28.93 $0.00 $32.55
11/07/03 3012 M1458 jackel $71.62 $0.00 $80.57 		straka01 (310)
203132 2003-12-26 01:25:00 Hi, this should do it. It presumes the following...

* you have your headings in row 1 of your sheets
* the destination for the customer data will be Sheet2.
* your database uses columns A-G,
* the customer numbers in column C.
* the activesheet must be your database sheet when you run the macro.

Sub CopyCustomerData()
'Copy selected customer rows into a new sheet.
Dim CustNum As String, Rng As Range, c, x

Do
CustNum = InputBox(prompt:="Enter Customer Number", _
Title:="Copy Customer Records")

With Range("c2:c" & Range("c65536").End(xlUp).Row)
Set c = .Find(CustNum, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
x = MsgBox("Invalid Customer Number. Try again?", vbYesNo, _
"Invalid Customer Number")
If x = 7 Then Exit Sub
Else
Exit Do
End If
End With
Loop

Rows("1:1").AutoFilter
Rows("1:1").AutoFilter Field:=3, Criteria1:=CustNum
Set Rng = Range("A2:G" & Range("G65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
Rng.Copy Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2, 1)
Rows.AutoFilter
End Sub

hth 		parry (27)
203133 2003-12-27 04:10:00 Excellent work, and such a brief program. I think this will be fine - initial tests look very promising. Thanks heaps - Merry Christmas & Happy New Year. straka01 (310)
203134 2003-12-27 05:08:00 My pleasure. Compliments of the season to you as well. :D parry (27)
1