Repeated Multiple Find

[rcmodelr]

I have a spreadsheet app.with the following format...

A B C D
E F
Around Left time Grower No. Driver
A1 RIVER ROAD
7:08 ENTT05282002 363 James
7:40 ENTT05282003 362 INA
1:58 9:06 ENTT05282005 377 James
2:05 9:45 ENTT05282007 360 INA
2:03 11:09 ENTT05282010 357 James
2:05 11:50 ENTT05283001 358 INA
2:08 1:17 ENTT05283003 365 James
A1 Total
2:03 Avg. Turn-around
A2 S & B FARMS
8:55 ENTT05282004 352 Jerry
9:34 ENTT05282006 361 S.INA
1:20 10:15 ENTT05282008 364 Jerry
1:26 11:00 ENTT05282009 354 S.INA
1:30 11:45 ENTT05282011 363 Jerry
1:40 ENTT05283004 362 INA
2:22 ENTT05283006 361 James
A2 Total
1:25 Avg. Turn-around
A3 WHIGHAM FARM
1:00 ENTT05283002 377 S.INA
2:00 ENTT05283005 352 Jerry
A3 Total

Grand Total

I'm working on an app that will take the files from the previous week and
make a Driver's Recap report for each driver formatted as follows...

TICKET #
DATE GROWER TRAILER # REG LOADS
10/10/05 S & B FARMS 352 ENTT05282004
10/10/05 S & B FARMS 364 ENTT05282008
10/10/05 S & B FARMS 363 ENTT05282011
10/10/05 WHIGHAM FARM 352 ENTT05283005
10/10/05 WHIGHAM FARM 365 ENTT05283008

So I need a routine that will look for the Driver's name, then once that
name is found, it should look UP to find the grower name that corresponds to
that trailer/ticket number, and place that grower name into the B column of
the Recap sheet between the run date and the corresponding trailer number.

I've tried using separate Find lines using SearchOrder:=xlNext to look for
the corresponding driver name, then another Find using
SearchOrder:=xlPrevious to look for the corresponding grower name. But this
results in the LAST grower name on the daily log sheet being indicated for
EVERY trailer the driver brought in that day, and the Find for the next
instance of that driver's name with the After:=Range(WhrLastFound) which
SHOULD tell it to continue the find from the spot where that driver's name
was LAST found. Instead, it results in several of the instances of that
driver's name being found 2 - 3 times, and some BLANK lines being placed by
the Find for Driver's name ending up pointing to a row with NO DRIVER/Ticket
data AT ALL

I found a work-around for the future where the daily file will repeat the
grower name in a cell of each row that does not print, but is strictly used
for the Recap sheet data, however my boss wants a routine that will also work
for the older files that ONLY have the grower name ABOVE the data from/for
each ticket that came from that grower.

Any ideas how this could be done SUCCESSFULLY???

Here is the Find code I have so far to work with the grower's name repeated
for EVERY ROW containing data for that grower...

Set found = .Cells.Find(What:=Loc)
If Not found Is Nothing Then
Loc = found.Address
Do
Whr = found.Row
' Get Grower name from SAME row
Workbooks("Recap").Worksheets(UseSht).Range("C" & (Drivers(j).RowCt)) = _
ActiveWorkbook.Worksheets(k).Range("BG" & (Whr))
' Get Corresponding Ticket Number
Workbooks("Recap").Worksheets(UseSht).Range("E" & (Drivers(j).RowCt)) = _
ActiveWorkbook.Worksheets(k).Range("D" & (Whr))
' Get Corresponding Trailer Number
Workbooks("Recap").Worksheets(UseSht).Range("D" & (Drivers(j).RowCt)) = _
ActiveWorkbook.Worksheets(k).Range("E" & (Whr))
' Place the Rund Date for this ticket/trailer
Workbooks("Recap").Worksheets(UseSht).Range("B" & (Drivers(j).RowCt)) = _
ActiveWorkbook.Worksheets(6).Range("B4")
Drivers(j).RowCt = Drivers(j).RowCt + 1
Set found = .Cells.FindNext(found)
Loop While found.Address > Loc

 

[Tom Ogilvy]

Set found = .Cells.Find(What:=Loc)
If Not found Is Nothing Then
Loc = found.Address
Do
Whr = found.Row
' Get Grower name from SAME row
'Workbooks("Recap").Worksheets(UseSht).Range("C" & (Drivers(j).RowCt)) = _
' ActiveWorkbook.Worksheets(k).Range("BG" & (Whr))
' assume the growers name is in column B = change to suit. Assumes drivers
column
' is not missing entries
if isempty(found.offset(-1,0)) then
Workbooks("Recap").Worksheets(UseSht).Range("C" & (Drivers(j).RowCt)) =
_
ActiveWorkbook.Worksheets(k).Range("B" & Whr -1)
else
set rng = found.end(xlup).offset(-1,0)
Workbooks("Recap").Worksheets(UseSht).Range("C" & (Drivers(j).RowCt))
= _
ActiveWorkbook.Worksheets(k).Range("B" & rng.row)
End if
' Get Corresponding Ticket Number
Workbooks("Recap").Worksheets(UseSht).Range("E" & (Drivers(j).RowCt)) = _
ActiveWorkbook.Worksheets(k).Range("D" & (Whr))
' Get Corresponding Trailer Number
Workbooks("Recap").Worksheets(UseSht).Range("D" & (Drivers(j).RowCt)) = _
ActiveWorkbook.Worksheets(k).Range("E" & (Whr))
' Place the Rund Date for this ticket/trailer
Workbooks("Recap").Worksheets(UseSht).Range("B" & (Drivers(j).RowCt)) = _
ActiveWorkbook.Worksheets(6).Range("B4")
Drivers(j).RowCt = Drivers(j).RowCt + 1
Set found = .Cells.FindNext(found)
Loop While found.Address > Loc

 

[rcmodelr]

Your revision might work MOST of the time, but usually with the first lot of
Day shift, and Last lot of Night shift...

The sheet is setup to allow night shift to run all their loads plus a few
extra loads if Day shift starts at the same farm.
The Day shift sheet is set to allow ALL the scheduled Day shift loads from
the first farm, PLUS a couple extra rows if night shift caught at the same
farm.

If the last load of night shift comes in only 1/2 full, but day shift starts
before all night shift's loads were run, normally the loads on the night
shift sheet will be in arrival sequence order, and any FULL loads night shift
left and the load being unloaded at shift change will end up getting copied
to the top of the Day shift sheet, and just deleted from the night shift
sheet resulting in 1 or more BLANK rows between the last load of night shift,
and the second-to-last load of night shift, with the Grower name somewhere
above the second-to-last night shift load row.

And when Day shift loads start coming in, often, the weighmaster will have
had the sheet set up with a couple extra rows for night shift carry-over. If
it looks like night shift might not finish their loads, the weighmaster will
often start entering the Day shift load data 1 to 3 rows BELOW the grower
name for that first lot.

Most reliable way to check for the existence of the Grower's name (since the
coding has no way of knowing the grower name in advance) would be to, before
assuming the grower name is in that row, check the A column, which contains
formula to calculate driver turn-around time. If the row being checked has
the Grower Name, the A column would have the LOT NUMBER.

Lot Number is ALWAYS only 2 characters in size. Night shift's lot numbers
as A1, A2 A3, etc... Day shift lot numbers as B1, B2, B3, etc

So to check for existence of grower's name in the row, best bet would be to
check for a lot number with a lot number mask variable set to check for a lot
number like "A?" for night shift lot sheet, and Like "B?" for the Day shift
sheet.

But how do I get that checked WHILE ALLOWING FOR any BLANK rows between the
row where the driver name was found and the row containing the Lot number and
Grower Name (Lot Number in Column A, Grower Name in column D, which is the
SAME column that contains the ticket number in the sheet this other program
is gathering its info for the individual Driver Recap pages?

 

[rcmodelr]

Here is how I thought of to handle it regardless of blank rows between load
info and grower name location. Is there some better way to do this? Any
built-in VBA options that would do this quicker and/or with less code?

Included sample includes the first part of the FindDrivers routine down to
and including the new coding I added above the code to actually copy the
contents from the source file to the destination file and page.

_______________

For k = 1 To 3 Step 2
Loc = Driver
Select Case k
Case Is = 1: Lotmask = "A?"
Case Is = 3: Lotmask = "B?"
End Select

With Worksheets(k)
("A1").Select
Set found = .Cells.Find(What:=Loc)
If Not found Is Nothing Then
Loc = found.Address
Do
Whr = found.Row
WhrFarm = Whr
Do
WhrFarm = WhrFarm - 1
FarmLoc = ActiveWorkbook.Worksheets(k).Cells(WhrFarm, 1)
Loop Until FarmLoc Like Lotmask Or WhrFarm < 10