Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Quick Fix... Find macro Issue
Im running a simple Find macro to populate cells from another file. Th
problem im having is when the term im finding is not there, my macr breaks and stops... Is there a way i can bypass or do something tha will jump to the next find if the first find was not found? Here's my Code: Windows("10.txt").Activate Cells.Find(WHAT:="DDCE", After:=ActiveCell, LookIn:=xlFormulas LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext MatchCase:= _ False).Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("E18").Select ActiveSheet.Paste Windows("10.txt").Activate Cells.Find(WHAT:="EXT", After:=ActiveCell, LookIn:=xlFormulas LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext MatchCase:= _ False).Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("J18").Select ActiveSheet.Past -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Quick Fix... Find macro Issue
Alex,
Dos this help Dim sSave As String Windows("10.txt").Activate sSave = ActiveCell.Address On Error Resume Next Cells.Find(WHAT:="DDCE", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False).Activate On Error GoTo 0 If ActiveCell.Address < sSave Then ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("E18").Select ActiveSheet.Paste Windows("10.txt").Activate Cells.Find(WHAT:="EXT", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("J18").Select ActiveSheet.Paste End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "alexm999 " wrote in message ... Im running a simple Find macro to populate cells from another file. The problem im having is when the term im finding is not there, my macro breaks and stops... Is there a way i can bypass or do something that will jump to the next find if the first find was not found? Here's my Code: Windows("10.txt").Activate Cells.Find(WHAT:="DDCE", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("E18").Select ActiveSheet.Paste Windows("10.txt").Activate Cells.Find(WHAT:="EXT", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("J18").Select ActiveSheet.Paste --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Quick Fix... Find macro Issue
Alex
Try On Error Resume Next where I've put it in your code Windows("10.txt").Activate On Error Resume Next Cells.Find(WHAT:="DDCE", After:=ActiveCell, LookIn:=xlFormulas,LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False).Activate Regards Peter -----Original Message----- Im running a simple Find macro to populate cells from another file. The problem im having is when the term im finding is not there, my macro breaks and stops... Is there a way i can bypass or do something that will jump to the next find if the first find was not found? Here's my Code: Windows("10.txt").Activate Cells.Find(WHAT:="DDCE", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("E18").Select ActiveSheet.Paste Windows("10.txt").Activate Cells.Find(WHAT:="EXT", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("J18").Select ActiveSheet.Paste --- Message posted from http://www.ExcelForum.com/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Quick Fix... Find macro Issue
Does help. Now one more problem. And I posted it before with no help...
I have 2 data criteria that Im searching for; DDC and DDCE. If I have no DDC in theory, the program should then skip to DDCE an use that data to populate the fields. but whats happening is if DDC i not there, DDCE data goes into DDC cells. Is there a way to isolate or stop this from happening? BTW, the other file that i get the DDC and DDCE data cannot b altered. Here's my new revised code: Windows("10.txt").Activate Set ofound = Cells.Find(WHAT:="DDC", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) If Not ofound Is Nothing Then ofound.Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("C18").Select ActiveSheet.Paste End If Windows("10.txt").Activate Set ofound = Cells.Find(WHAT:="DDCE", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not ofound Is Nothing Then ofound.Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=6).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("E18").Select ActiveSheet.Paste End I -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Quick Fix... Find macro Issue
The problem is you are looking for xlpart rather than xlwhole. Since DDCE
contains DDC, the first finds DDCE when DDC is missing and copies its associated value to C18, the second find then finds DDCE again and copies its associated value to E18 Since you are reading in a text file, you probably need to keep the argument as xlPart, because there are likely to be spaces on the end of the string. So you need to add an extra test to make sure you have found DDC vice DDCE Sub AAAATester() ' ' Look for DDC ' Windows("10.txt").Activate Set ofound = Cells.Find(WHAT:="DDC", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) ' ' copy won't happen unless the DDC was found ' if found, destination is cell C18 ' If Not ofound Is Nothing Then ofound.Activate if Trim(ofound) = "DDC" then ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("C18").Select ActiveSheet.Paste End if End If ' ' Now look for DDCE ' Windows("10.txt").Activate Set ofound = Cells.Find(WHAT:="DDCE", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) ' ' Copy won't occur unless DDCE is found ' if found it is copied to E18 ' If Not ofound Is Nothing Then ofound.Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=6).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("E18").Select ActiveSheet.Paste End If End Sub -- Regards, Tom Ogilvy "alexm999 " wrote in message ... Does help. Now one more problem. And I posted it before with no help... I have 2 data criteria that Im searching for; DDC and DDCE. If I have no DDC in theory, the program should then skip to DDCE and use that data to populate the fields. but whats happening is if DDC is not there, DDCE data goes into DDC cells. Is there a way to isolate or stop this from happening? BTW, the other file that i get the DDC and DDCE data cannot be altered. Here's my new revised code: Windows("10.txt").Activate Set ofound = Cells.Find(WHAT:="DDC", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True) If Not ofound Is Nothing Then ofound.Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=5).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("C18").Select ActiveSheet.Paste End If Windows("10.txt").Activate Set ofound = Cells.Find(WHAT:="DDCE", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not ofound Is Nothing Then ofound.Activate ActiveCell.Offset(rowOffset:=0, columnOffset:=6).Activate Selection.Copy Windows("DAILY OPERATIONS_2004.xls").Activate Range("E18").Select ActiveSheet.Paste End If --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Quick Fix... Find macro Issue
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace issue in macro | New Users to Excel | |||
Quick Find in long drop down list | New Users to Excel | |||
data validation quick find | Excel Discussion (Misc queries) | |||
Shortcut Keys -- quick find | Excel Discussion (Misc queries) | |||
excel quick find format | Excel Worksheet Functions |