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

Using Find Function with Macros
 

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" wrote in message
...
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

Using Find Function with Macros
 

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.

Using Find Function with Macros
 

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.

Using Find Function with Macros
 

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." wrote in message
oups.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.

Using Find Function with Macros
 

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

Using Find Function with Macros
 

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." wrote in message
oups.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