Using Find Function with Macros

I have a file with Column A, containing accounts number and to the right, 7
columns with different information related to the accounts, each account
could have 1 to hundreds of rows, what I am trying to do is with a Macro use
"Find" function and look for a specific account but the account to be input
in message box.
I try to do it with the Macro record but I can only get a specific account.
Could I have some help.

Thanks

Fernando

Hi Fernando

can we take it one step further - why are you trying to do this with a macro
.... is it part of a larger macro, is it to copy the information to another
sheet or just to print it
because personally i'ld use either data / filter / autofilter from the menu
to do this or use the autofilter in code rather than find

Cheers
JulieD

"Fgomez" <fernangoca@yahoo.ca> wrote in message
news:%23h7DOXjKFHA.1396@TK2MSFTNGP10.phx.gbl...
>I have a file with Column A, containing accounts number and to the right, 7
> columns with different information related to the accounts, each account
> could have 1 to hundreds of rows, what I am trying to do is with a Macro
> use
> "Find" function and look for a specific account but the account to be
> input
> in message box.
> I try to do it with the Macro record but I can only get a specific
> account.
> Could I have some help.
>
> Thanks
>
> Fernando
>
>

Hi, I think I understand what you are trying to do. If I am wrong let
me know, but give this a try:

Sub FindAcct()
Dim Acct$
'Assuming alpha numeric account number
'If it is a number then use Type:=1
Acct = Application.InputBox(prompt:="Enter Account", Type:=2)
Range("A1").Select
Cells.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

HTH--Lonnie M.

Just to clarify, if your account is a number you would have to Dim the
Acct varriable as an accebtable number and use type:=1 in the inputbox.

Sub FindAcct()
Dim Acct&
Acct = Application.InputBox(prompt:="Enter Account", Type:=1)

Cells.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

Just as Julie mentioned there are probably much better ways to
accomplish what you are trying to do. So if you give us a better Idea
of what you want to do we can probably point you in that direction.

Thanks Lonnie
This is very close to what I am looking for, just I am getting an error
saying "Object doesn,t support this property or method" and is highlighting
from Range("A1") to the end.
Any idea what could be happening.

Fernando


"Lonnie M." <lonnie_r_m@hotmail.com> wrote in message
news:1110985037.198709.187710@g14g2000cwa.googlegroups.com...
> Hi, I think I understand what you are trying to do. If I am wrong let
> me know, but give this a try:
>
> Sub FindAcct()
> Dim Acct$
> 'Assuming alpha numeric account number
> 'If it is a number then use Type:=1
> Acct = Application.InputBox(prompt:="Enter Account", Type:=2)
> Range("A1").Select
> Cells.Find(What:=Acct, After:=ActiveCell, LookIn:=xlFormulas _
> , LookAt:=xlPart, SearchOrder:=xlByRows,
> SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
> End Sub
>
> HTH--Lonnie M.
>

If you pasted this in, it may have broken some of the lines where it
shouldn't have.
When I tried pasting this it placed "SearchDirection:=xlNext, _" on
it's own line, and I had to bring it back up to the previous line. If
this happened it should be pretty easy to tell because most likely a
portion of the code will be colored red.

HTH--Lonnie

Thanks Lonnie,
Yes, that happend but I fixed it and same message came out, but what I did
is to add first line of what I got with the macro record which is
Columns("A:A").Select
Selection.Find(What:=Acct, After........

and works fine.

Thanks a lot for your help


"Lonnie M." <lonnie_r_m@hotmail.com> wrote in message
news:1110986747.772976.120110@l41g2000cwc.googlegroups.com...
> If you pasted this in, it may have broken some of the lines where it
> shouldn't have.
> When I tried pasting this it placed "SearchDirection:=xlNext, _" on
> it's own line, and I had to bring it back up to the previous line. If
> this happened it should be pretty easy to tell because most likely a
> portion of the code will be colored red.
>
> HTH--Lonnie
>