Can someone help me make this code more robust?
 

As part of a data manipulation / data prep macro I need to locate certain
fields and copy their values to a new location. The way I try to do this is
to search for a caption cell to that field and then to select the data
relative to the caption field, then copy that data and past it in a new
location.

Here is a code sample:
=========================
'Locate the field that contains the main process name
Sheets(Sheetname).Select
Range("A4").Select
Cells.Find(What:="Significant Process:", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select

Application.CutCopyMode = False
Selection.Copy
Sheets("Import This").Select
ActiveSheet.Paste

=========================

Unfortunately some of the worksheets I need to convert are inconsistent in
their format. Some workbooks don't even have the field I am looking for. So
let's say I am looking for a cell with the value :="Significant Process:"
and it just doesn't exist. Right now the macro just bombs out and I have to
go to debug and figure out what to do next. Generally the process is the
same, I skip the procedure for manipulating that particular piece of data
and go onto the next set of data that I am looking for.

What I don't know is how to trap a "Data Not Found" error using Excel's
search capability. This is one thing I need help with.


In addition, I have noted that in some cases there may actually be more than
one cell with the same data string in it. I have sort of bypassed this
problem by starting my search at a specified location. I know that the
search can be ordered by specified directions, so if I start at a cell that
would be just before the potential location of the string I am looking for I
can be pretty sure I am hitting the one I want. Are there other strategies
I could use here that might say alert me to the existence of multiple cells
with the same data?

--
Henry Stock, Network Administrator
onProject.com
3 Wing Drive
Cedar Knolls, NJ 07927-1006

Can someone help me make this code more robust?
 

Dim rng as Range
set rng = Nothing
'Locate the field that contains the main process name
Sheets(Sheetname).Select
Range("A4").Select
set rng = Cells.Find(What:="Significant Process:", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
if not rng is nothing then
set rng1 = rng.offset(0,1)
rng.copy
Sheets("Import This").Activate
ActiveSheet.Paste
else
Sheets("Import This").Activate
End if
' move to next entry location

--
Regards,
Tom Ogilvy

"Henry Stock" wrote in message
...
As part of a data manipulation / data prep macro I need to locate certain
fields and copy their values to a new location. The way I try to do this
is
to search for a caption cell to that field and then to select the data
relative to the caption field, then copy that data and past it in a new
location.

Here is a code sample:
=========================
'Locate the field that contains the main process name
Sheets(Sheetname).Select
Range("A4").Select
Cells.Find(What:="Significant Process:", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select

Application.CutCopyMode = False
Selection.Copy
Sheets("Import This").Select
ActiveSheet.Paste

=========================

Unfortunately some of the worksheets I need to convert are inconsistent
in
their format. Some workbooks don't even have the field I am looking for.
So
let's say I am looking for a cell with the value :="Significant Process:"
and it just doesn't exist. Right now the macro just bombs out and I have
to
go to debug and figure out what to do next. Generally the process is the
same, I skip the procedure for manipulating that particular piece of data
and go onto the next set of data that I am looking for.

What I don't know is how to trap a "Data Not Found" error using Excel's
search capability. This is one thing I need help with.


In addition, I have noted that in some cases there may actually be more
than
one cell with the same data string in it. I have sort of bypassed this
problem by starting my search at a specified location. I know that the
search can be ordered by specified directions, so if I start at a cell
that
would be just before the potential location of the string I am looking for
I
can be pretty sure I am hitting the one I want. Are there other
strategies
I could use here that might say alert me to the existence of multiple
cells
with the same data?

--
Henry Stock, Network Administrator
onProject.com
3 Wing Drive
Cedar Knolls, NJ 07927-1006