Excel 2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aemorri - 13 Aug 2007 16:48 GMT

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. --
:/

Reply to this Message

Don Guillett - 13 Aug 2007 20:38 GMT

Look in the vba help index for FIND & FINDNEXT

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Trying to convert a program written over 20 years ago from Lotus 1.2.3
> into
[quoted text clipped - 10 lines]
> macro button. --
> :/

Reply to this Message

aemorri - 13 Aug 2007 21:38 GMT

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.

Signature

> Look in the vba help index for FIND & FINDNEXT
>
[quoted text clipped - 12 lines]
> > macro button. --
> > :/

Reply to this Message

Don Guillett - 13 Aug 2007 22:09 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Thanks but that does not help me. If I have an employee who's numer is
> 47,
[quoted text clipped - 22 lines]
>> > macro button. --
>> > :/

Reply to this Message

aemorri - 14 Aug 2007 00:00 GMT

I will give that a try. Thank you.

Signature

> With Worksheets(1).Range("a1:a500")
> Set c = .Find(2, lookin:=xlValues,LOOKAT:=XLWHOLE)
[quoted text clipped - 33 lines]
> >> > macro button. --
> >> > :/

Reply to this Message

aemorri - 22 Aug 2007 19:22 GMT

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? --
:/

> I will give that a try. Thank you.
>
[quoted text clipped - 35 lines]
> > >> > macro button. --
> > >> > :/

Reply to this Message

Don Guillett - 22 Aug 2007 23:16 GMT

send me a workbook if you like.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>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
[quoted text clipped - 50 lines]
>> > >> > macro button. --
>> > >> > :/

Reply to this Message

aemorri - 23 Aug 2007 00:48 GMT

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.

Signature

> send me a workbook if you like.
>
[quoted text clipped - 52 lines]
> >> > >> > macro button. --
> >> > >> > :/

Reply to this Message

Don Guillett - 23 Aug 2007 15:04 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> 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
[quoted text clipped - 67 lines]
>> >> > >> > macro button. --
>> >> > >> > :/

Reply to this Message

Don Guillett - 23 Aug 2007 15:11 GMT

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

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I sent back
>
[quoted text clipped - 93 lines]
>>> >> > >> > macro button. --
>>> >> > >> > :/

Reply to this Message