adding rows
Posted by Ian M on August 01, 2001 6:46 AM
is there a way to add a row using an if statement. I want to be able to insert a row if the row above it meets a certain criteria
Posted by Mark W. on August 01, 2001 7:03 AM
Not with an IF() function. Excel functions only
return values to the cells in which they're entered.
Functions and don't take action; however, commands
such as Insert | Rows do take action. If you want
to test a condition and then perform a command
then you'll need to create and use a macro.
Posted by Cory on August 01, 2001 7:11 AM
Sure! Here's one that I use to look at a column of numbers and insert a row where there's a difference. ex:
col A
11
11
11
14
14
2
8
8
8
This code inserts a line after each group of numbers:
Private Sub PutARowIn()
Range("A2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
End If
Loop
End Sub
Have Fun!
Cory
Posted by Ian M on August 01, 2001 8:14 AM
Thanks alot! just one more question though. is it possible to change your code around so that it can look for a few spacific words and then put a space after it?
Posted by Ian M on August 01, 2001 8:16 AM
oops! i dont mean a space i mean adding a row. : This code inserts a line after each group of numbers
Posted by Cory on August 03, 2001 3:00 PM
Ian, try this... It may be a little buggish, but it worked on my mockup:
Private Sub CommandButton1_Click()
Range("A2").Select
Do Until ActiveCell.Value = Empty
Cells.Find(What:="your specific words", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Offset(1, 0).Value = Empty Then
Exit Sub
End If
ActiveCell.Offset(1, 0).EntireRow.Insert
Loop
Range("A1").Select
End Sub
Cory
