Need Quick Fix... Find macro Issue

[alexm999]

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

 

[Bob Phillips]

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)

 

[Peter Atherton]

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

 

[alexm999]

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

 

[Tom Ogilvy]

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

 

[alexm999]

Trim did the trick... Thanks