insert row

[straus]

Hello,

In the database below, I want to a row to be inserted before every Row where the credit class is "I". Can this be done with the help of a macro? Pls help

CR. CLASS A/c No. Amnt Status
I 1280021514 2431500 active
G 1280035621 3454353 not active
G 3580026521 3454387 active
G 9580025141 9879898 not active
S 1280049936 3621548 not active
D 3580026214 242342 not active
I 8880022221 4334355 active
P 1218005075 34544 not active
Q 1597999218 44414 not active
Q 1977997360 495694 not active
I 2357996503 54724 active
R 2737994645 59879 not active
I 3117993788 65034 not active
R 4564564564 701 active
R 8564564564 75344 not active

 

[indiantrix]

Although I don't do VBA, this is the code produced when I recorded a macro with your data. It seems to work. Others may offer more expert advice, of course.
' Keyboard Shortcut: Ctrl+i
'
Cells.Find(What:="I", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

 

[attc]

this is similar to a problem I had, except instead of inserting a row, I wanted to make things bold, underlined and other stuff. you might be able to use something like this:

Sub InsertRow()
'
Application.ScreenUpdating = False

Dim FOUND As Range
Dim firstR As Integer

Set FOUND = Cells.Find(What:="I", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Let firstR = FOUND.Row + 1

Do
FOUND.Activate
FOUND.EntireRow.Insert

Set FOUND = Cells.Find(What:="I", After:=ActiveCell.Offset(1, 0), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Loop Until (FOUND.Row = firstR)

End Sub


[warning:] this might not be particularly robust, e.g. if there are NO "I"s or there are some credit classes consisting of more than 1 character that could also include an "I", etc.

ps. I just realised also that it inserts a row before any row where there is an "I" and not just in col.A
This message was edited by attc on 2002-10-26 13:31

 

[rikrak]

Hi Straus,

Try this simple macro:
<pre>
Sub insertRowsWhereCreditclassIsI()
Dim row As Long
row = 2 'skip headings
While Cells(row, 1) <> ""
If UCase(Cells(row, 1)) = "I" Then
Rows(row).Insert Shift:=xlDown
row = row + 1
End If
row = row + 1
Wend
End Sub
</pre>

 

[attc]

yeh, that's a much better solution.

 

[straus]

that was superb..Rickrack...just what I wanted...you guys are great...thanks a ton

gratefully yours,
straus