Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a Bar code ??? | Excel Discussion (Misc queries) | |||
Are there functions that perform robust statistics in Excel? | Excel Worksheet Functions | |||
How to make VBA code to self-destroy itself? | Excel Programming | |||
Make code more economical | Excel Programming | |||
Make a Change to Code | Excel Programming |