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 he
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
|