Thread: update row numbers after different active cells in macros followi.
View Single Post
  #11   Report Post  
LMIV
 
Posts: n/a
Default


"Dave Peterson" wrote:

Sometimes it's almost too easy to use the copy|paste method of programming.

I think that when you come back to it to make changes, sometimes that simple
loop is easier to understand/modify.



LMIV wrote:

"Dave Peterson" wrote:

Glad you got it working.

Does this mean that you modified the code to look for adios and deleted rows
based on that found cell?

It seems like it should work.

LMIV wrote:

<<snipped
Also, Dave, as you saw in previous posting no modification was done, with adios, since the delete line function works rather well with this simple macro:

ub OLDCALC()
'
' OLDCALC Macro
' Macro recorded 2/8/2005 by Don Davis
'
' Keyboard Shortcut: Ctrl+b
'
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
Range("F36").Select
End Sub

see you...

--

Dave Peterson

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

Reply With QuoteReply With Quote