Loop Routine in Excel
 

Looking to get a macro that once it sees a variable in a particular table
inserts 3 rows and inserts two rows in the space created.

Then moves down to the next occurance of that variable and does the same-
until it reacheses the end of the file

Any help would be much appreciated
--
Thanks for your help

Loop Routine in Excel
 

For i = Range("A1").End(xlDown).Row To 1 Step -1
If Cells(i,"A").alue = "the_value" Then
Rows(i+1,Resize(3).insert
End If
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"David" wrote in message
...

Looking to get a macro that once it sees a variable in a particular table
inserts 3 rows and inserts two rows in the space created.

Then moves down to the next occurance of that variable and does the same-
until it reacheses the end of the file

Any help would be much appreciated
--
Thanks for your help

Loop Routine in Excel
 

I am not to sure what you mean by "inserts 3 rows and inserts two rows in the
space created" but this will insert 3 rows at every instance of "x" in a Cell
in Column A...

Sub DeleteStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="x", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry nothin found"
Else
strFirstAddress = rngFound.Address
Do
rngFound.Offset(1, 0).Resize(3).EntireRow.Insert
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub
--
HTH...

Jim Thomlinson


"David" wrote:


Looking to get a macro that once it sees a variable in a particular table
inserts 3 rows and inserts two rows in the space created.

Then moves down to the next occurance of that variable and does the same-
until it reacheses the end of the file

Any help would be much appreciated
--
Thanks for your help

Loop Routine in Excel
 

JIm,

Thanks very much for this

The insert bit is a range of cells that needs to go in the blank cells created

e.g insert 3 rows- then copy & past a range in the first row created.

Could you help with this last bit?

Thanks again,

David

--
Thanks for your help


"Jim Thomlinson" wrote:

I am not to sure what you mean by "inserts 3 rows and inserts two rows in the
space created" but this will insert 3 rows at every instance of "x" in a Cell
in Column A...

Sub DeleteStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="x", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry nothin found"
Else
strFirstAddress = rngFound.Address
Do
rngFound.Offset(1, 0).Resize(3).EntireRow.Insert
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub
--
HTH...

Jim Thomlinson


"David" wrote:


Looking to get a macro that once it sees a variable in a particular table
inserts 3 rows and inserts two rows in the space created.

Then moves down to the next occurance of that variable and does the same-
until it reacheses the end of the file

Any help would be much appreciated
--
Thanks for your help