update row numbers after different active cells in macros followi.

in macros row numbers remain fixed after a spreadsheet has been added a 
series of new rows in specific locations based on the location of the various 
active cells following a find command.

can this be improved by automatically opening up correct new rows based on 
changing active cell?
0
LMIV (7)
2/8/2005 3:57:05 PM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
636 Views

Similar Articles

[PageSpeed] 40

you can refer to the row of the cell that contained the value of the Find:

dim FoundCell as range
with activesheet
 set foundcell = .cells.find(what:=.....)
end with

if foundcell is nothing then
  'not found
else
  msgbox foundcell.row
end if

But I bet this isn't quite what you're asking....

LMIV wrote:
> 
> in macros row numbers remain fixed after a spreadsheet has been added a
> series of new rows in specific locations based on the location of the various
> active cells following a find command.
> 
> can this be improved by automatically opening up correct new rows based on
> changing active cell?

-- 

Dave Peterson
0
ec357201 (5290)
2/9/2005 12:22:29 AM

"Dave Peterson" wrote:

> you can refer to the row of the cell that contained the value of the Find:
> 
> dim FoundCell as range
> with activesheet
>  set foundcell = .cells.find(what:=.....)
> end with
> 
> if foundcell is nothing then
>   'not found
> else
>   msgbox foundcell.row
> end if
> 
> But I bet this isn't quite what you're asking....
> 
> LMIV wrote:
> > 
> > in macros row numbers remain fixed after a spreadsheet has been added a
> > series of new rows in specific locations based on the location of the various
> > active cells following a find command.
> > 
> > can this be improved by automatically opening up correct new rows based on
> > changing active cell?
> 
> -- 
> 
> Dave Peterson
> Hi Dave Petersion and thank you.

Here's what I did after that question was posed here:

Since thre are ten rows/lines of information that receive/lose entries due 
to the various contents of the "find" command, I decided to do nine different 
macros, for each entry inclusion, called hola01-hola09, and then invoke each 
particular macro depending on the key value position in the "find" command.

Let me illustrate this with the actual code:

Sub hola01()
'
' hola01 Macro
' Macro recorded 2/8/2005 by Don Davis
'
' Keyboard Shortcut: Ctrl+q
'
    Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K363").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K377").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K395").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K410").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K429").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K444").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K459").Select
    Selection.EntireRow.Insert
    Range("A459").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("A414").Select
    ActiveWindow.SmallScroll Down:=-40
    Range("A362").Select
End Sub

This will add successively a new rowe on the second line of the range where 
it needs to go. If  I want to open up a different row, based on a different 
"find" content, I will use the corresponding macro, such as (3 lines down):

Sub hola04()
'
' hola04 Macro
' Macro recorded 2/8/2005 by Don Davis
'
' Keyboard Shortcut: Ctrl+r
'
    Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K366").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K380").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K398").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K413").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K432").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K447").Select
    Selection.EntireRow.Insert
    Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("K462").Select
    Selection.EntireRow.Insert
    Range("A462").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("A418").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("A362").Select
End Sub


Now, this is extremely cumbursome, ansd possibly open to inaccuracies as 
line numbers change, but it is the best I have got so far.

I will try to implement your method, though.
0
LMIV (7)
2/9/2005 5:13:02 AM
It's pretty difficult to figure out how many rows down you want to go based on
your code--it just selects a specific range.

But maybe this will give you an idea.  (By the way, a lot of this code is stolen
from the example in VBA's help):

Option Explicit
Sub hola01()

    Dim FoundCell As Range
    Dim FirstAddress As String
    Dim wks As Worksheet
    Dim HowManyRowsBelow As Long
    Dim myStrings As Variant
    Dim iCtr As Long
       
    HowManyRowsBelow _
            = Application.InputBox("How many Rows Below the foundcell?", _
                                           Type:=1)
    
    If HowManyRowsBelow < 1 Then
        Exit Sub
    End If
    
    'keep adding as many as you want
    myStrings = Array("hola01", "hola02", "hola03", _
                      "hola04", "hola05", "hola06")
    
    Set wks = ActiveSheet
    
    With wks
        For iCtr = LBound(myStrings) To UBound(myStrings)
            With .UsedRange
                Set FoundCell = .Cells.Find(What:=myStrings(iCtr), _
                                    After:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlPart, SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False, _
                                    SearchFormat:=False)
                                    
                If FoundCell Is Nothing Then
                    'do nothing
                Else
                    FirstAddress = FoundCell.Address
                    Do
                        FoundCell.Offset(HowManyRowsBelow + 1).EntireRow.Insert
                        Set FoundCell = .FindNext(FoundCell)
                    Loop While Not FoundCell Is Nothing _
                      And FoundCell.Address <> FirstAddress
                End If
            End With
        Next iCtr
    End With

End Sub

Test it against a copy of your worksheet--just in case!


LMIV wrote:
> 
> "Dave Peterson" wrote:
> 
> > you can refer to the row of the cell that contained the value of the Find:
> >
> > dim FoundCell as range
> > with activesheet
> >  set foundcell = .cells.find(what:=.....)
> > end with
> >
> > if foundcell is nothing then
> >   'not found
> > else
> >   msgbox foundcell.row
> > end if
> >
> > But I bet this isn't quite what you're asking....
> >
> > LMIV wrote:
> > >
> > > in macros row numbers remain fixed after a spreadsheet has been added a
> > > series of new rows in specific locations based on the location of the various
> > > active cells following a find command.
> > >
> > > can this be improved by automatically opening up correct new rows based on
> > > changing active cell?
> >
> > --
> >
> > Dave Peterson
> > Hi Dave Petersion and thank you.
> 
> Here's what I did after that question was posed here:
> 
> Since thre are ten rows/lines of information that receive/lose entries due
> to the various contents of the "find" command, I decided to do nine different
> macros, for each entry inclusion, called hola01-hola09, and then invoke each
> particular macro depending on the key value position in the "find" command.
> 
> Let me illustrate this with the actual code:
> 
> Sub hola01()
> '
> ' hola01 Macro
> ' Macro recorded 2/8/2005 by Don Davis
> '
> ' Keyboard Shortcut: Ctrl+q
> '
>     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K363").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K377").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K395").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K410").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K429").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K444").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K459").Select
>     Selection.EntireRow.Insert
>     Range("A459").Select
>     ActiveWindow.LargeScroll Down:=-1
>     Range("A414").Select
>     ActiveWindow.SmallScroll Down:=-40
>     Range("A362").Select
> End Sub
> 
> This will add successively a new rowe on the second line of the range where
> it needs to go. If  I want to open up a different row, based on a different
> "find" content, I will use the corresponding macro, such as (3 lines down):
> 
> Sub hola04()
> '
> ' hola04 Macro
> ' Macro recorded 2/8/2005 by Don Davis
> '
> ' Keyboard Shortcut: Ctrl+r
> '
>     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K366").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K380").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K398").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K413").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K432").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K447").Select
>     Selection.EntireRow.Insert
>     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
>         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
>         False, SearchFormat:=False).Activate
>     Range("K462").Select
>     Selection.EntireRow.Insert
>     Range("A462").Select
>     ActiveWindow.LargeScroll Down:=-1
>     Range("A418").Select
>     ActiveWindow.LargeScroll Down:=-1
>     Range("A362").Select
> End Sub
> 
> Now, this is extremely cumbursome, ansd possibly open to inaccuracies as
> line numbers change, but it is the best I have got so far.
> 
> I will try to implement your method, though.

-- 

Dave Peterson
0
ec357201 (5290)
2/9/2005 10:40:53 PM
Hi Dave Peterson:

From cumbersome,, you have turned things around to brilliant.
The below code was simply altered by removing the  "+1" out of the
"FoundCell.Offset(HowManyRowsBelow + 1).EntireRow.Insert" statement.

This caused the sub to insert an empty line right below the search found 
line, exactly where it was needed/wanted.


Let me illustrate the achievement:

BEFORE

1998	48142	$393,891								hola01
2001	66841	$546,884								0
2002	11118	$90,966								0
2002	27000	$220,910								0
2003	344	$2,815								0
2003	56853	$465,163								0
1998	165168	$1,351,382								0
					
										
1998	48142	$7,600	$15,787	$5,266	$10,521	$7,900			hola01
2001	66841	$6,600	$9,874	$14,522	-$4,648					0
2002	11118	$10,088	$90,736	$43,430	$47,305	$16,900				0
2002	27000	$10,100	$37,407	$43,430	-$6,023	$14,500				0
2003	344	$19,100	$5,552,326	$53,850	$5,498,476			0
2003	56853	$11,100	$19,524	$53,850	-$34,326					0
1998	165168	$2,727	$1,651	$5,266	-$3,615					0
						
										
										
										
										

1998	48142	$76,881	$69,281	$5,885	Good					hola01
2001	66841	$49,934	$43,334	$9,370	Good					0
2002	11118	$408,290	$398,202	$14,300	Excellent			0
2002	27000	$174,266	$164,166	$13,680	Good				0
2003	344	$24,385,970	$24,366,870	$17,225	Excellent		0
2003	56853	$96,783	$85,683	$12,175	Good					0
1998	165168	$9,973	$7,246	$3,400	Good					0

										
										

1998	-1858	912%								hola01
2001	16841	657%								0
2002	-38882	3947%								0
2002	-23000	1625%								0
2003	-49656	127575%								0
2003	6853	772%								0
1998	115168	266%								0
						
										
										
										
										
										
										

1998	30000	$6,200	$3,050							hola01
2001	20000	$9,043	$9,814				30000			0
2002	-40000	$18,550	$11,530	
2002	0	$13,299	$15,300	
2003	-10000	$16,600	$20,401							0
2003	0	$14,633	$14,600				50000			0
1998	30000	$6,200	$1,513			10/23/04				0
					
										

1998	48142	$121,143	2.52							hola01
2001	66841	$153,115	2.29							0
2002	11118	$131,973	11.87							0
2002	27000	$104,644	3.88							0
2003	344	$6,106,121	17750.35							0
2003	56853	$146,920	2.58							0
1998	165168	$342,570	2.07							0

										
										

1998:19:59	-1858	$1,400	-$75,481	perfo/vario	0:00:05	10521	1998	48142	5	hola01
2001:17:59	16841	-$2,443	-$52,376	south/vario	2:00:03	-4648	2001	66841	3	
2002:08:59	-38882	-$8,462	-$416,752	golde/harri	0:05:06	47305	2002	11118	6	
2002:08:59	-23000	-$3,199	-$177,465	wrang/undet	0:00:02	-6023	2002	27000	2	
2003:18:59	-49656	$2,500	-$24,383,470	presti/vario	0:00:07
2003	344	7	
2003:17:59	6853	-$3,533	-$100,316	river/vario	2:00:01	-34326	2003	56853	1	
1998:24:59	115168	-$3,473	-$13,446	pms76/vario	0:00:04	-


AFTER

1998	48142	$393,891								hola01
										
2001	66841	$546,884								0
2002	11118	$90,966								0
2002	27000	$220,910								0
2003	344	$2,815								0
2003	56853	$465,163								0
1998	165168	$1,351,382								0
						
										

1998	48142	$7,600	$15,787	$5,266	$10,521	$7,900		hola01
										
2001	66841	$6,600	$9,874	$14,522	-$4,648					0
2002	11118	$10,088	$90,736	$43,430	$47,305	$16,900				0
2002	27000	$10,100	$37,407	$43,430	-$6,023	$14,500				0
2003	344	$19,100	$5,552,326	$53,850	$5,498,476			0
2003	56853	$11,100	$19,524	$53,850	-$34,326					0
1998	165168	$2,727	$1,651	$5,266	-$3,615					0
									
										
										
										
										

1998	48142	$76,881	$69,281	$5,885	Good					hola01
										
2001	66841	$49,934	$43,334	$9,370	Good					0
2002	11118	$408,290	$398,202	$14,300	Excellent			0
2002	27000	$174,266	$164,166	$13,680	Good				0
2003	344	$24,385,970	$24,366,870	$17,225	Excellent		0
2003	56853	$96,783	$85,683	$12,175	Good					0
1998	165168	$9,973	$7,246	$3,400	Good					0

										
										
1998	-1858	912%								hola01
										
2001	16841	657%								0
2002	-38882	3947%								0
2002	-23000	1625%								0
2003	-49656	127575%								0
2003	6853	772%								0
1998	115168	266%								0

										
						
	
1998	30000	$6,200	$3,050							hola01
										
2001	20000	$9,043	$9,814				30000			0
2002	-40000	$18,550	$11,530	h
2002	0	$13,299	$15,300	
2003	-10000	$16,600	$20,401							0
2003	0	$14,633	$14,600				50000			0
1998	30000	$6,200	$1,513			10/23/04				0
			
										

1998	48142	$121,143	2.52							hola01
										
2001	66841	$153,115	2.29							0
2002	11118	$131,973	11.87							0
2002	27000	$104,644	3.88							0
2003	344	$6,106,121	17750.35							0
2003	56853	$146,920	2.58							0
1998	165168	$342,570	2.07							0

										
										

1998:19:59	-1858	$1,400	-$75,481	perfo/vario	0:00:05	10521
										
2001:17:59	16841	-$2,443	-$52,376	south/vario	2:00:03	-4648	
2002:08:59	-38882	-$8,462	-$416,752	golde/harri	0:05:06	47305	2002:08:59	-23000	-$3,199	-$177,465	wrang/undet	0:00:02	-6023	
2003:18:59	-49656	$2,500	-$24,383,470	presti/vario	0:00:07		7	
2003:17:59	6853	-$3,533	-$100,316	river/vario	2:00:01	-34326		
1998:24:59	115168	-$3,473	-$13,446	pms76/vario	0:00:04	RGS						
I had to trim some of the actual lines information to show you the 
appearance in this MS formatted reply box.

This is now independent of any added/deleted lines elsewhere, and works 
wonderful for any line added within any year sequence.

This is the opposite direct-record macro for deleted lines, which 
works--surprisingly--well after all.

Sub 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
    Range("A362").Select
End Sub

Thank you, Sir!


"Dave Peterson" wrote:

> It's pretty difficult to figure out how many rows down you want to go based on
> your code--it just selects a specific range.
> 
> But maybe this will give you an idea.  (By the way, a lot of this code is stolen
> from the example in VBA's help):
> 
> Option Explicit
> Sub hola01()
> 
>     Dim FoundCell As Range
>     Dim FirstAddress As String
>     Dim wks As Worksheet
>     Dim HowManyRowsBelow As Long
>     Dim myStrings As Variant
>     Dim iCtr As Long
>        
>     HowManyRowsBelow _
>             = Application.InputBox("How many Rows Below the foundcell?", _
>                                            Type:=1)
>     
>     If HowManyRowsBelow < 1 Then
>         Exit Sub
>     End If
>     
>     'keep adding as many as you want
>     myStrings = Array("hola01", "hola02", "hola03", _
>                       "hola04", "hola05", "hola06")
>     
>     Set wks = ActiveSheet
>     
>     With wks
>         For iCtr = LBound(myStrings) To UBound(myStrings)
>             With .UsedRange
>                 Set FoundCell = .Cells.Find(What:=myStrings(iCtr), _
>                                     After:=.Cells(.Cells.Count), _
>                                     LookIn:=xlValues, _
>                                     LookAt:=xlPart, SearchOrder:=xlByRows, _
>                                     SearchDirection:=xlNext, _
>                                     MatchCase:=False, _
>                                     SearchFormat:=False)
>                                     
>                 If FoundCell Is Nothing Then
>                     'do nothing
>                 Else
>                     FirstAddress = FoundCell.Address
>                     Do
>                         FoundCell.Offset(HowManyRowsBelow + 1).EntireRow.Insert
>                         Set FoundCell = .FindNext(FoundCell)
>                     Loop While Not FoundCell Is Nothing _
>                       And FoundCell.Address <> FirstAddress
>                 End If
>             End With
>         Next iCtr
>     End With
> 
> End Sub
> 
> Test it against a copy of your worksheet--just in case!
> 
> 
> LMIV wrote:
> > 
> > "Dave Peterson" wrote:
> > 
> > > you can refer to the row of the cell that contained the value of the Find:
> > >
> > > dim FoundCell as range
> > > with activesheet
> > >  set foundcell = .cells.find(what:=.....)
> > > end with
> > >
> > > if foundcell is nothing then
> > >   'not found
> > > else
> > >   msgbox foundcell.row
> > > end if
> > >
> > > But I bet this isn't quite what you're asking....
> > >
> > > LMIV wrote:
> > > >
> > > > in macros row numbers remain fixed after a spreadsheet has been added a
> > > > series of new rows in specific locations based on the location of the various
> > > > active cells following a find command.
> > > >
> > > > can this be improved by automatically opening up correct new rows based on
> > > > changing active cell?
> > >
> > > --
> > >
> > > Dave Peterson
> > > Hi Dave Petersion and thank you.
> > 
> > Here's what I did after that question was posed here:
> > 
> > Since thre are ten rows/lines of information that receive/lose entries due
> > to the various contents of the "find" command, I decided to do nine different
> > macros, for each entry inclusion, called hola01-hola09, and then invoke each
> > particular macro depending on the key value position in the "find" command.
> > 
> > Let me illustrate this with the actual code:
> > 
> > Sub hola01()
> > '
> > ' hola01 Macro
> > ' Macro recorded 2/8/2005 by Don Davis
> > '
> > ' Keyboard Shortcut: Ctrl+q
> > '
> >     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K363").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K377").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K395").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K410").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K429").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K444").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola01", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K459").Select
> >     Selection.EntireRow.Insert
> >     Range("A459").Select
> >     ActiveWindow.LargeScroll Down:=-1
> >     Range("A414").Select
> >     ActiveWindow.SmallScroll Down:=-40
> >     Range("A362").Select
> > End Sub
> > 
> > This will add successively a new rowe on the second line of the range where
> > it needs to go. If  I want to open up a different row, based on a different
> > "find" content, I will use the corresponding macro, such as (3 lines down):
> > 
> > Sub hola04()
> > '
> > ' hola04 Macro
> > ' Macro recorded 2/8/2005 by Don Davis
> > '
> > ' Keyboard Shortcut: Ctrl+r
> > '
> >     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K366").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K380").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K398").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K413").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K432").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K447").Select
> >     Selection.EntireRow.Insert
> >     Cells.Find(What:="hola04", After:=ActiveCell, LookIn:=xlValues, LookAt _
> >         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> >         False, SearchFormat:=False).Activate
> >     Range("K462").Select
> >     Selection.EntireRow.Insert
> >     Range("A462").Select
> >     ActiveWindow.LargeScroll Down:=-1
> >     Range("A418").Select
> >     ActiveWindow.LargeScroll Down:=-1
> >     Range("A362").Select
> > End Sub
> > 
> > Now, this is extremely cumbursome, ansd possibly open to inaccuracies as
> > line numbers change, but it is the best I have got so far.
> > 
> > I will try to implement your method, though.
> 
> -- 
> 
> Dave Peterson
> 
0
LMIV (7)
2/10/2005 2:05:03 AM
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>>
0
ec357201 (5290)
2/10/2005 2:26:41 AM

"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>>
> Hi, Dave
as usual, many activities underway, but clearly your code has made a huge 
difference; now, in terms of further automation you can see that what I am 
doing is fairly simply: you get to a row with information on a car model 
year, open a fresh line for additional information, input it, and so on.

In this next pice of code i have combined parts of your macrodave with 
direct recording portions to achieve the placement of the data on the 
previous line right onto the newly opened one, such as a duplication would 
do; however this is not working due to some compile error in the second 
offset code line. If this can be done, with minor updfates/changes one can 
follow up on eBay car auction details very quickly. This is the currect 
testmacrodave:

Sub TESTNEWCALC()
'
' TESTNEWCALC Macro
' Macro recorded 2/13/2005 by Don Davis
'
' Keyboard Shortcut: Ctrl+m
'
    
' ADDEDMACRODAVE Macro
' Macro recorded 2/9/2005 by Don Davis

    Dim FoundCell As Range
    Dim FirstAddress As String
    Dim wks As Worksheet
    Dim HowManyRowsBelow As Long
    Dim myStrings As Variant
    Dim iCtr As Long
     
    HowManyRowsBelow _
            = Application.InputBox("How many rows below the foundcell?", _
                                          Type:=1)
    HowManyRowsAbove _
            = Application.InputBox("How many rows above the foundcell?", 
Type:=2)
        
    
    If HowManyRowsBelow < 1 Then
        
             
        Exit Sub
    End If
   
    '(this adds an empty line below found cell line) keep adding as many as 
you want
    myStrings = Array("hola", "hola01", "hola02", "hola03", _
                      "hola04", "hola05", "hola06")
   
    Set wks = ActiveSheet
   
    With wks
        For iCtr = LBound(myStrings) To UBound(myStrings)
            With .UsedRange
                Set FoundCell = .Cells.Find(What:=myStrings(iCtr), _
                                    After:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlPart, SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False, _
                                    SearchFormat:=False)
                                   
                If FoundCell Is Nothing Then
                    'do nothing
                Else
                    FirstAddress = FoundCell.Address
                    Do
                        FoundCell.Offset(HowManyRowsBelow).EntireRow.Insert
                        FoundCell.Ofsset(HowManyRowsAbove).EntireRow.Select
                        ActiveSheet.Paste
                        Set FoundCell = .FindNext(FoundCell)
                    Loop While Not FoundCell Is Nothing _
                      And FoundCell.Address <> FirstAddress
                End If
            End With
        Next iCtr
    End With
End Sub

Again thank you for your tremendous assistance, which I hope you enjoy 
giving...
0
LMIV (7)
2/13/2005 3:47:03 PM

"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...
0
LMIV (7)
2/13/2005 3:49:03 PM

"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>>
> > Hi, Dave
> as usual, many activities underway, but clearly your code has made a huge 
> difference; now, in terms of further automation you can see that what I am 
> doing is fairly simply: you get to a row with information on a car model 
> year, open a fresh line for additional information, input it, and so on.
> 
> In this next pice of code i have combined parts of your macrodave with 
> direct recording portions to achieve the placement of the data on the 
> previous line right onto the newly opened one, such as a duplication would 
> do; however this is not working due to some compile error in the second 
> offset code line. If this can be done, with minor updfates/changes one can 
> follow up on eBay car auction details very quickly. This is the currect 
> testmacrodave:
> 
> Sub TESTNEWCALC()
> '
> ' TESTNEWCALC Macro
> ' Macro recorded 2/13/2005 by Don Davis
> '
> ' Keyboard Shortcut: Ctrl+m
> '
>     
> ' ADDEDMACRODAVE Macro
> ' Macro recorded 2/9/2005 by Don Davis
> 
>     Dim FoundCell As Range
>     Dim FirstAddress As String
>     Dim wks As Worksheet
>     Dim HowManyRowsBelow As Long
>     Dim myStrings As Variant
>     Dim iCtr As Long
>      
>     HowManyRowsBelow _
>             = Application.InputBox("How many rows below the foundcell?", _
>                                           Type:=1)
>     HowManyRowsAbove _
>             = Application.InputBox("How many rows above the foundcell?", 
> Type:=2)
>         
>     
>     If HowManyRowsBelow < 1 Then
>         
>              
>         Exit Sub
>     End If
>    
>     '(this adds an empty line below found cell line) keep adding as many as 
> you want
>     myStrings = Array("hola", "hola01", "hola02", "hola03", _
>                       "hola04", "hola05", "hola06")
>    
>     Set wks = ActiveSheet
>    
>     With wks
>         For iCtr = LBound(myStrings) To UBound(myStrings)
>             With .UsedRange
>                 Set FoundCell = .Cells.Find(What:=myStrings(iCtr), _
>                                     After:=.Cells(.Cells.Count), _
>                                     LookIn:=xlValues, _
>                                     LookAt:=xlPart, SearchOrder:=xlByRows, _
>                                     SearchDirection:=xlNext, _
>                                     MatchCase:=False, _
>                                     SearchFormat:=False)
>                                    
>                 If FoundCell Is Nothing Then
>                     'do nothing
>                 Else
>                     FirstAddress = FoundCell.Address
>                     Do
>                         FoundCell.Offset(HowManyRowsBelow).EntireRow.Insert
>                         FoundCell.Ofsset(HowManyRowsAbove).EntireRow.Select
>                         ActiveSheet.Paste
>                         Set FoundCell = .FindNext(FoundCell)
>                     Loop While Not FoundCell Is Nothing _
>                       And FoundCell.Address <> FirstAddress
>                 End If
>             End With
>         Next iCtr
>     End With
> End Sub
> 
> Again thank you for your tremendous assistance, which I hope you enjoy 
> giving...

Getting back to the OLDCALC macro, it might have been erroneos, here's an 
actual code that works great to delete old lines...

Sub OLDCALC()
'
' OLDCALC Macro
' Macro recorded 2/13/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...
0
LMIV (7)
2/13/2005 4:03:01 PM
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
0
ec357201 (5290)
2/14/2005 12:23:48 AM

"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 are:

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
> 
0
LMIV (7)
2/15/2005 4:23:02 PM
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.count), _
                    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 are:
> 
> 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
0
ec357201 (5290)
2/16/2005 12:44:21 AM
Reply:

Similar Artilces:

Cell Editing Macro
I am looking for a way to edit a cell and put quotation marks around the contents of that cell using a macro (or some way of automating this). The reason being that this file is being imported into another program and the syntax requires quotation marks around each value. The original file is comma delimited and the contents are both numbers and words. Any help towards a solution is greatly appreciated. thanks Tyler mcleant at hotmail dot com Styler, Try writing the file with the Text Write program at www.tushar-mehta.com. It has an option to put all fields in quotes. -- Regards fro...

Two different outlook programs on my desktop?
Can I have two different outlook mail programs running on my desktop using two different servers (Xplornet-satelight system, and Sympatico-dialup)? If I can, do I have to download the second Outlook, or how do I get another blank Outlook to start from scratch with for my new email address? My husband and I need two different accounts and would like to have both using Outlook. If this means anything, I am using Vista. Would it work if I switch to another user name/profile when I log onto my computer? That would be very easy then... Thanks, Ann -- gouligann no, yo...

Update for MS Money 2005?
I have Money 2005. Is there an update to MS Money for Canadian users? Thanks in advance for any answers. We need a bit more information! Are you having problems with M2005 or is this just a post-Christmas/New Year random query when you are trying to get away from the in-laws? -- Regards Bob Peel, Microsoft MVP - Money For unofficial FAQs see http://money.mvps.org/ or http://umpmfaq.info/ I do not respond to any emails that I have not specifically asked for. "Daniel" <Daniel@discussions.microsoft.com> wrote in message news:E86EAB89-21DE-4505-ACAD-647278D736BD@microso...

updating sheets based on data in first sheet
Another payroll question, I have a workbook that contains 26 sheets, one for each bi-weekl payroll period. I would like to set it up so when i add a new employe the rest of the sheets also update automatically with that employee name and information. I have been able to acheive this to a limited degree using th =sheet1!a1 formula, but this only updates the info in the first cel and particularly the first column. I would like to acheive this using the first sheet, since at th end of the year I would like to be able to calculate ytd figure easily. Thank -- Message posted from http://www.Exc...

what command shifts all cells in a column up or down?
Accidentally did something in an Excel worksheet to shift all cells in a particular column down. Do not know the command(s) or keystrokes to reverse what I did and shift all cells in the column up. select the inserted cell Edit>Delete when asked, click "Shift cells up" -- Kind regards, Niek Otten "marco" <marco@discussions.microsoft.com> wrote in message news:C0CAFF80-291E-4A62-9D52-980D94F74378@microsoft.com... > Accidentally did something in an Excel worksheet to shift all cells in a > particular column down. Do not know the command(s) or keystrokes...

Problem with Script Updating
I am using a script to update the “1099 Type” field for Master Vendor table. The script basically is a basic if-then statement. This script is run “Before Document Commit” and it’s not updating correctly, its flip-flopping the results. When the script is set to run Before Document Commit I have the “Destination mapping” field “1099 Type” set to “Use Script”. Script below: If SourceFields("Send 1099") = "N" Then DestinationFields("Options.1099 Type").Value = 1 Else DestinationFields("Options.1099 Type").Value = 4 End If I have also tried this s...

Publisher 2007 and Microsoft Updates
I created a newsletter yesterday - everything fine - use Publisher all the time. Then an automatic update came down this morning and I can't open the file - it says "Publisher cannot open file" Other documents in Publisher are opening - any ideas? http://support.microsoft.com/kb/972566/ -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Kim" <Kim@discussions.microsoft.com> wrote in message news:8754339B-99DC-4D09-83AD-6B34D8215274@microsoft.com... >I created a newsletter yesterday - everythin...

Microsoft Update only updates Windows Defender
iacrosoft Update only updates Windows Defender - I have to use Belarc Advisor to get a list of missing updates, and then go to the Microsoft Download Center to search for the missing updates and download and install them one at a time. I have Windows XP Home SP3, with Microsoft Update set to automatic download - can anyone tell me why I do not get all the necessary upadtes? Is this a sudden, new problem? When was Automatic Updates last working? Can you update manually via http://windowsupdate.microsoft.com? Any chance you've got a beta build of WinXP SP3 installed? What...

Update question
Soory if this is the wrong place to ask this, but cannot find a NG proper to Windows 7. I currently run Windows Vista and am getting tired of Vista's decision to tell me that it is going to shut down in less than a minute. So have decided to upgrade to Windows &. I note that I can purchase an upgrade versiom for 64 quid from Amazon or an apparently full version of Windows 7 Home premium for 89 quid. My question is this - If I buy the upgrade version will I only be able to load it on a new PC in the future if I already have windows Vista installed? i.e For any future cl...

Outlook 2003 died mysteriously after update
Setup in a nutshell... System: Lenovo T500 laptop OS: Vista Business SP1 32-bit Software: Office Outlook 2003 SP3 Problem... So I'm using this setup for over a year, everything OK. Yesterday I did two things and now Outlook dies (quietly, sans error message) immediately after startup. It show the splash screen, displays my inbox, and *poof* disappears. What I did earlier... 1. Windows Update installed: KB9766662, KB979306, KB979099, KB975929 2. At the same time, while searching for another program to uninstall I stumbled upon (and uninstalled) Windows LIVE Toolbar and...

Excel cells changes number format automatically
I am using Excel 2007. Many of the cells I am working on are in general format where I store various numbers for analysis. These values change itself to date format occassionally creating whole lot of work for me to change them back to general format. How can I solve this issue? Also in my pivot table, my source data is in general format. Still when I click on sub total in any catagory in the table to find details about it, formats are changed date format in the new sheet it pops up. Can someone help resolve this issue. ...

Counting cells with a specific background colour
I have a spreadsheet with several rows. The background of the rows can either be coloured Red, Orange, Green or white. What I want to do is have a running total for each colour, so that if a row is set with a green background it will increment the Green total by one. If an Orange row is changed to Red, the Orange count will drop by one, and the Red count will go up by one. Is this possible, and if so, how? Rgds Duncan One way: Assuming that the colors aren't applied by Conditional Formatting: http://cpearson.com/excel/colors.htm If they are applied by CF, do a conditional...

How update entity in post update?
I created a handler for Update post callout for Opportunity. I want update some fields of the opportunity on the PostUpdate. However, if I call the Update method of CRMOpportunity in the PostUpdate I will create a recursive post callout. Can somebody help me? Thank you for pay attention []'s Vin�cius Pitta Lima de Ara�jo You need to check the OrigObjectXML field to see what fields were updated and then act appropriately. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Wed, 4 Aug 2004 17:43:17 -0300, "Vin�cius ...

Merge into same document using different data sources
I have a template file that I use as a mailer. Each week I set up my merge fields and merge from an excel data file. The following week I want everything to look exactly the same but want to merge from a different excel list. I open the publication, tell it to proceed without connection to the datasource (my old list), choose tools, mail merge, use an existing list ... and all of my perfectly placed merge fields disappear! So every time I want to use another list I have to put my merge fields in again. My headers are the same in every excel file so mapping should not be a problem. ...

Auto color-shading of rows
Can I automate an excel worksheet such that if ,say, "436" is written in a certain cell it shades that row yellow, or if "437" is in that cell it shades the row blue. It would then be easy to visually tell in an excel chart which lines belonged to ,say, specific companies. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case 436...

count matching numbers
This is probably simple but I'm about to go crazy trying to figure it out. Please help. I'm trying to count or total how many cells match another row of cells. For instance A4 = 23 B4 = 14 A5 = 34 B5 = 30 A6 = 39 B6 = 34 A7 = 48 B7 = 39 A8 = 53 B8 = 55 The answer I'm looking for is 2 because there are two cells that match. (A5 matches B6 and A6 matches B7) Thanks, mike Try ths: =SUMPRODUCT(--(ISNUMBER(MATCH(A4:A8,B4:B8,0)))) -- Biff Microsoft Excel MVP "Catfish" <mtblanton@gmail.com> wrote in message news:491279e0-18f9-4d29-a...

Display Message While Running Macro
I have a macro that does quote a few things and takes for 5 to 1 seconds to run. Ive noticed that users of my sheet somewhat freak ou because the macro runs thru several pages and the users think that th computer has a problem. Is there a way i can have a message box o blank page with a message displayed that has no buttons (OK/YES) tha shows only while the macro is running to tell the user what th workbook is doing. Something simple not very good with VB Thank -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/mem...

Cannot install update KB979906 for .NET Framework 1.1 SP1
Running : Windows XP media center edition SP3 Have tried installing KB979906 a few times and also downloaded update manually but cannot install this update. Receiving error 0x643.Is it safe to uninstall the .NET Framework 1.1 and re-install as possibly corrupt , without having to uninstall/re-install all other .NET frameworks 2 , 3 and 3.5 including the updates that go with them? .. Hello sherlockomes, you might want to look at the following kb article to see if this will help with the error 80070643. <http://windows.microsoft.com/en-US/windows-vista/Windows-Update-error...

How to format an entire row a certain color depending on the value in a cell?
Howdy, Can anyone of you excel gods help me with the following? I have a small spreedsheet (excel 2003) set up like the following to help me keep track of my music collection: A B C D 1 Title Artist Year Format (1=LP, 2=CD, 3=Tape) 2 HardDaysNight Beatles 1966 1 3 Crossroads Eric Clapton 1985 2 I would like to know at a glance by color coding, what the ratio of LPs,CDs,&Tapes make up my collection. I've figured o...

latest update too MSCFV2
Hi, I have MSCFV2 version 6.5.7825.0. Could someone inform me if this is the latest download? Looks like you have 6.5.7825.0 from 05/21/2006, but there is a newer version - 6.5.7831.0 from 06/01/2006. C. Smith Enso Technologies, Incorporated http://www.ensotech.com On Tue, 13 Jun 2006 04:25:02 -0700, Paul <Paul@discussions.microsoft.com> wrote: >Hi, > >I have MSCFV2 version 6.5.7825.0. Could someone inform me if this is the >latest download? Christopher Smith csmith@ensotech.com Enso Technologies, Incorporated http://www.ensotech.com Also - meant to post this in t...

Print record once, update Yes/No field verifiying print
I would like to print a group of records, then have a Yes/No field [Printed] updated in my Jobs table with an update query showing the records were printed. Then next time the report runs, In my query criteria I will test for True values on the Yes/No field. Then only the records with the Yes/No field marked No will print. Any suggestions? Thanks Tommyboy,there's more to this question than meets the eye. For an explanation of what's involved, see: Has the record been printed? at: http://allenbrowne.com/ser-72.html The article includes a free sample database that dem...

Dynamic Menu update
Hi All, I need to update a menu item dynamically, setting its SetCheck property to either true or false, based on a user operation. I am trying to do this from a custom function. Usually, the pCmdUI pointer is used. But how do I do it from another function? Thanks Your "other function" should change some setting. In your ON_UPDATE_COMMAND_UI handler, you should check that setting and call SetCheck as needed. -------------- Ajay Kalra ajaykalra@yahoo.com In addition to Ajay's response, this page may help you: http://msdn2.microsoft.com/en-us/library/6kc4d8f...

how to return a serial number to a date
Example: 40899 Is there a formula to convert the serial number 40899 above back to 12/22/2011? Thanks, Holly Format the cell as Date -- Biff Microsoft Excel MVP "Holly" <Holly@discussions.microsoft.com> wrote in message news:82F628DD-B506-4A72-B5AC-DF85265ADAB0@microsoft.com... > Example: 40899 > > Is there a formula to convert the serial number 40899 above back to > 12/22/2011? > > Thanks, Holly "Holly" wrote: > Is there a formula to convert the serial number 40899 > above back to 12/22/2011? It would h...

Validation of numbers
hey I want to validate a cell so that it will only accept numbers, no text. These numbers could be decimals or whole numbers, therefore th Whole number option won't work. I tried the Decimal option, but i still allowed text. Can u help?? Thx KrisB_baco -- Message posted from http://www.ExcelForum.com One way (assume A1 is the cell) =ISNUMBER(A1) -- Regards, Peo Sjoblom "KrisB_bacon >" <<KrisB_bacon.11vmuj@excelforum-nospam.com> wrote in message news:KrisB_bacon.11vmuj@excelforum-nospam.com... > hey > > I want to validate a cell so that it will ...

Stop duplicate numbers
Hi I am setting up a spreadsheet to use as a basis for a database. How do I create a column of unique numbers that can be used as serial numbers? I need to ensure that all the numbers are not duplicated. Thanks Dave Put 1 in A1, 2 in A2, select A1:A2, go to the bottom right of the selection until the cursor becomes a black cross, click and drag down as much as you want. -- HTH RP (remove nothere from the email address if mailing direct) "Stig" <qazat@yahoo.com> wrote in message news:wIOdnflaPtux-3fcRVnyvQ@brightview.com... > Hi > I am setting up a spreadsheet t...