Excel 2000

[Guest]

Trying to convert a program written over 20 years ago from Lotus 1.2.3 into
Excel 2000. Problem is I can't find the verbage for the visual basic command
for going to a database and finding only certain criteria, copying it and
pasting it into another sheet. Example: Each employee has an employee
number, there are multiple entries in the data base for each employee. I
want to extract the information for only one of those employees and put it
onto another sheet. The database is added to dailey so it needs to be
expandable. Each month a new database is started for the next pay period. I
need this in a command that I can write into visaul basic and connect to a
macro button. --
:/

 

[Don Guillett]

Look in the vba help index for FIND & FINDNEXT

 

[Guest]

Thanks but that does not help me. If I have an employee who's numer is 47,
it finds all employees with the number 4 or 7 in them. I need to make it
only see the one I am looking for.

 

[Don Guillett]

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues,LOOKAT:=XLWHOLE)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[Guest]

I will give that a try. Thank you.
--
:/

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues,LOOKAT:=XLWHOLE)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Guest]

I could not get this to work. I am sure it does but I don't think I am
understanding what you suggested. Could you translate it in more detail? --
:/

 

[Don Guillett]

send me a workbook if you like.

 

[Guest]

OK, It's more like a whole program but here goes. I realy appreciate any
help you may be able to give me. I will send it to you but you will probably
need to ask me some question about what I am trying to do. Also, The buttons
are not yet connected to antything and I have been working on two types of
call buttons, one in menu form and then the buttons. I will decide which one
to use later.

 

[Don Guillett]

I sent back

Sub findemployee()
what = InputBox("Enter Employee Number")
With Worksheets("Database")
lr = .Cells(Rows.Count, "a").End(xlUp).Row
With .Range("a1:a500")
Set c = .Find(what, LookIn:=xlValues, LOOKAT:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox "Row " & c.Row
MsgBox c.Offset(, 1)

'whatever you want to do with your find(s)

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

 

[Don Guillett]

lr = .Cells(Rows.Count, "a").End(xlUp).Row

With .Range("a1:a" & lr)