Your code does a lot of selecting and activating.

Instead of lines like these:

Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:X1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select

You could use something like:

dim FoundCell as range
dim destCell as range
with activesheet
set foundcell = .cells.find(what:="hola",after:=.cells(.cells.coun t), _
lookin:=xlvalues, lookat:=xlpart, _
searchorder:=xlbyrows,searchdirection:=xlnext, _
matchcase:=false, searchformat:=false)
if foundcell is nothing then
'it wasn't found--what should be done?
else
foundcell.offset(1,0).entirerow.insert
set destcell = foundcell.offset(1,0).entirerow.cells(1)
foundcell.entirerow.cells(1).resize(1,24).copy _
destination:=destcell
end if
End with

Be careful. I typed this in the email window, so it may contain lots of typos.

You may want to look at that original reply for other ideas, too.


LMIV wrote:


Hi, Dave

Reflecting on past communications I see now that eventually, even though not
very elegant, one finds what the search asks for. In my case, please take a
look at both macros below and see what you think: They simply automate rows
with new information as well as delete unnecesssary lines. This was the
result of several trials until I came up with the tiny button where you can
decide to record a macro using relative row/cell referencesm which was the
crux of the problem, initially.

Here there a

Sub RELNEWCALC2005()
'
' RELNEWCALC2005 Macro
' Macro recorded 2/15/2005 by Don Davis
'
' Keyboard Shortcut: Ctrl+r
'
Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:X1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:Y1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:C1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:G1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:F1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:C1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:G1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:D1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Range("A1").Select
Cells.Find(What:="hola", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, -29).Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1:I1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-390, 5).Range("A1").Select
End Sub

Sub RELOLDCALC2005()
'
' RELOLDCALC2005 Macro
' Macro recorded 2/15/2005 by Don Davis
'
' Keyboard Shortcut: Ctrl+t
'
Range("A1").Select
ActiveCell.Offset(50, 0).Range("A1").Select
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Cells.Find(What:="adios", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
ActiveCell.Offset(-2, -24).Range("A1").Select
End Sub

Everything is cool, again, for a while anyhow...

LMIV


--

Dave Peterson