delivery db in vb

  • Thread starter Thread starter jbabb
  • Start date Start date
J

jbabb

I am trying to process home delivery orders in excel.. I
made a page where when I imput the tel. no. of the
client, his information, if exists, is brought to this
screen form a given db, but I am using FOR and NEXT with
a variable to look for the inputed tel no. in the db in
the correspondin column, the variable being the row no.,
once it is found I bring the rest of the info. to the
main page(address, name, etc.)

I cant use vlookup because if the client does not exist
the cells must be filled by hand, in doing so, I would
erase the vlookp formula.

It is working, the problem is that now it takes quite a
while to find a client since my db is bigger now.

Is there a faster command in excel vb to do this? if so,
how?

Please respond to (e-mail address removed)

thanks
 
The following piece of VB code reads the telephone number
typed in cell A2 of sheet "Orders", lloks for a matching
telephone no in column A of sheet "DB", and if found,
displays the contents of the three cells to the right of
the tel no in "DB" in the three cells to the right of A2
in "Orders", or dispays a "not found message" if no match
is found. You can change the sheet/column/cell references
and add more cells, if required, easily.
I tested this with a database of almost 5,000 customers,
and it still does the job in a flash.

Sub get_cust()
Sheets("Order").Select
Range("A2").Select
For i = 1 To 3
ActiveCell.Offset(0, i).ClearContents
Next
telno = ActiveCell.Value2
Sheets("DB").Select
Columns("A").Select
On Error GoTo not_found
Selection.Find(What:=telno, After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
lnam = ActiveCell.Offset(0, 1).Value2
fnam = ActiveCell.Offset(0, 2).Value2
addr = ActiveCell.Offset(0, 3).Value2
rw = ActiveCell.Row
Sheets("Order").Select
ActiveCell.Offset(0, 1).Value2 = lnam
ActiveCell.Offset(0, 2).Value2 = fnam
ActiveCell.Offset(0, 3).Value2 = addr
Exit Sub

not_found:
Sheets("Order").Select
Range("A2").Select
MsgBox ("Phone number not in database")

End Sub

Nikos Y. ([email protected])
 
Back
Top