Conditional search and copy

[TSmith]

While I have written a few simple VBA macros before, this one is making my head hurt. I am trying to figure out how to search a named range and if the specific condition is met, copy certain cells to another worksheet. Specifically, I have a worksheet (Trip Cards) that contains 40 "cards" of information. Each card occupies 20 rows by 16 columns and they are arranged two cards across and 10 cards down. I need to search the named range "TripNum" and if the cell on a given card contains the letter C, copy certain cells to the Cancelation worksheet. The cells to be copies are offset from the Trip Number cell on the first card as follows:

Trip Number is D1
Date is G1
Group is C5
Driver is B19

The offsets will reamin the same for all other cards and be indexed against the Trip Number cell that contains the "C". For each card to be copies, I need it to go to the next available line on the destination worksheet.

Any help would be deeply appreciated.

Tim

[Chuckzilla]

Couple of quick questions...

Would a "C" appear in any of the cells in the range?
Are there instances where a "C" could appear but they would not be valid?

If any C would be acceptable you could use Find and FindNext to locate them.

Available row is easy:

FirstEmptyRow = Worksheets("MySheet").Range("A65536").End(XLUP).Row + 1

provided Range A will always have data...

Chuck

[TSmith]

The C would only appear if there was a cancellation. The named range "TripNum" refers on the specific cells where the C would appear. I thought if I used a name for the range, I could somehow condition the search to only those 40 cells.

Thanks
Tim

[Chuckzilla]

does each "card" have it's own named range or is there one named range for the 40 cards?

Chuck

[TSmith]

The named range covers all of the cards. For example, the range is
$d$1,$l$1,$d$22,$l$22 etc.

Tim

[Chuckzilla]

Do you have an example sheet you could post?

[TSmith]

Here is the Trip Card spreadsheet. The "C" for a canceled trip goes in the Trip Number cell. If a "C" is entered, I want the data to be copied to the Cancellation sheet.