I have an Excel 2003 spreadsheet with Lastname Firstname and Teamname as
columns A-C. The sheet is sorted on these three columns in the order stated.
Columns D thru T contain other teamnames played.

I am writing a macro that put's up an InputBox requesting the user to enter
a Lastname, Firstname, Teamname separated by commas such as:
Lastname2,Jill,Team1 to find the row for "Jill Lastname2".

I used the macro recorder to create a macro that does a Find on Col A then B
then C. Problem is, when I find the Lastname in Col A then select Column B
and issue a Find command for the Firstname, the Find starts from the top of
column B when I want it to begin with the row that contained the lastname
successfully found in column A. There is an After: property associated with
the Find command but I don't know how to assign the row number where the
lastname was found to that After: property.


Sample Data

Lastname1 Jill Team1
Lastname2 Jill Team1
Lastname2 Mary Team1

When I enter "Lastname2,Jill,Team1" the first Find in my macro successfully
finds row 2, but the next Find stops at the Jill name in Column B identifying
the wrong row.

Any and all help is appreciated.

Jack

This may help
http://www.ozgrid.com/VBA/advanced-find.htm

--
Steve

"JackGombola" wrote in message
...
I have an Excel 2003 spreadsheet with Lastname Firstname and Teamname as
columns A-C. The sheet is sorted on these three columns in the order
stated.
Columns D thru T contain other teamnames played.

I am writing a macro that put's up an InputBox requesting the user to
enter
a Lastname, Firstname, Teamname separated by commas such as:
Lastname2,Jill,Team1 to find the row for "Jill Lastname2".

I used the macro recorder to create a macro that does a Find on Col A then
B
then C. Problem is, when I find the Lastname in Col A then select Column
B
and issue a Find command for the Firstname, the Find starts from the top
of
column B when I want it to begin with the row that contained the lastname
successfully found in column A. There is an After: property associated
with
the Find command but I don't know how to assign the row number where the
lastname was found to that After: property.


Sample Data

Lastname1 Jill Team1
Lastname2 Jill Team1
Lastname2 Mary Team1

When I enter "Lastname2,Jill,Team1" the first Find in my macro
successfully
finds row 2, but the next Find stops at the Jill name in Column B
identifying
the wrong row.

Any and all help is appreciated.

Jack

Hello Jack,

I don't know what you want to do with the data after finding and this
suggestion might not be suitable but have you thought about using AutoFilter?

If you have not used it before then simply click anywhere in your data and
Select the menu item Data - Filter - AutoFilter. (This is a toggle action
so you turn it off the same way.)

You then only have to click the drop down arrows and make your selections on
the various columns. (Note that if you only select a value in the Team column
then it will display the entire team.)

Editing of the code is required on recorded macros using Find otherwise the
code will fail and stop if it does not find the required data.

If this suggestion will not do what you want and you still want to run with
the Find then post the code you have so far and I will have a look at it.

--
Regards,

OssieMac


"JackGombola" wrote:

I have an Excel 2003 spreadsheet with Lastname Firstname and Teamname as
columns A-C. The sheet is sorted on these three columns in the order stated.
Columns D thru T contain other teamnames played.

I am writing a macro that put's up an InputBox requesting the user to enter
a Lastname, Firstname, Teamname separated by commas such as:
Lastname2,Jill,Team1 to find the row for "Jill Lastname2".

I used the macro recorder to create a macro that does a Find on Col A then B
then C. Problem is, when I find the Lastname in Col A then select Column B
and issue a Find command for the Firstname, the Find starts from the top of
column B when I want it to begin with the row that contained the lastname
successfully found in column A. There is an After: property associated with
the Find command but I don't know how to assign the row number where the
lastname was found to that After: property.


Sample Data

Lastname1 Jill Team1
Lastname2 Jill Team1
Lastname2 Mary Team1

When I enter "Lastname2,Jill,Team1" the first Find in my macro successfully
finds row 2, but the next Find stops at the Jill name in Column B identifying
the wrong row.

Any and all help is appreciated.

Jack

Thanks. I hadn't considered the AutoFilter. I'll check it out. jg

"OssieMac" wrote:

Hello Jack,

I don't know what you want to do with the data after finding and this
suggestion might not be suitable but have you thought about using AutoFilter?

If you have not used it before then simply click anywhere in your data and
Select the menu item Data - Filter - AutoFilter. (This is a toggle action
so you turn it off the same way.)

You then only have to click the drop down arrows and make your selections on
the various columns. (Note that if you only select a value in the Team column
then it will display the entire team.)

Editing of the code is required on recorded macros using Find otherwise the
code will fail and stop if it does not find the required data.

If this suggestion will not do what you want and you still want to run with
the Find then post the code you have so far and I will have a look at it.

--
Regards,

OssieMac


"JackGombola" wrote:

I have an Excel 2003 spreadsheet with Lastname Firstname and Teamname as
columns A-C. The sheet is sorted on these three columns in the order stated.
Columns D thru T contain other teamnames played.

I am writing a macro that put's up an InputBox requesting the user to enter
a Lastname, Firstname, Teamname separated by commas such as:
Lastname2,Jill,Team1 to find the row for "Jill Lastname2".

I used the macro recorder to create a macro that does a Find on Col A then B
then C. Problem is, when I find the Lastname in Col A then select Column B
and issue a Find command for the Firstname, the Find starts from the top of
column B when I want it to begin with the row that contained the lastname
successfully found in column A. There is an After: property associated with
the Find command but I don't know how to assign the row number where the
lastname was found to that After: property.


Sample Data

Lastname1 Jill Team1
Lastname2 Jill Team1
Lastname2 Mary Team1

When I enter "Lastname2,Jill,Team1" the first Find in my macro successfully
finds row 2, but the next Find stops at the Jill name in Column B identifying
the wrong row.

Any and all help is appreciated.

Jack