Offset Command?

[CindyA]

I have the following macro looking for the word "No" in column I.
After finding the word "No" I would like to have the macro move BELOW the cell in which the word "No" resides, and THEN insert a blank row. This code below finds the word "No" and inserts a blank row ABOVE that row; I need to insert the row below. I can't seem to find the proper code for "move 1 cell below before inserting a blank row". Thanks in advance.

*********************
Sub Test1()
Dim c As Range, myCount As Long, i As Long

myCount = Application.CountIf(Range("I1:I20000"), "No")

With Range("I1:I20000")
i = 1
Set c = .Find("No", LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.EntireRow.Insert
Set c = .FindNext(c)
i = i + 1
Loop While Not c Is Nothing And i <= myCount
End If
End With

End Sub

 

[colbymack]

Put this in to offset your cell.

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

HTH,
Colbymack

 

[CindyA]

Offset command?

I inserted the line you suggested as shown below. Doesn't do anything different. Did I put it in the wrong spot? I need to find the cell containing "No" and insert a blank row BELOW that row.

Thanks.
********************

Sub Test1()
Dim c As Range, myCount As Long, i As Long

myCount = Application.CountIf(Range("I1:I20000"), "No")

With Range("I1:I20000")
i = 1
Set c = .Find("No", LookIn:=xlValues)
If Not c Is Nothing Then
Do
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activatec.EntireRow.Insert
Set c = .FindNext(c)
i = i + 1
Loop While Not c Is Nothing And i <= myCount
End If
End With

End Sub

 

[colbymack]

Sorry - this was somewhat new to me, but I found that this should work for what you have asked about:

Sub Test1()
Dim c As Range, myCount As Long, i As Long

myCount = Application.CountIf(Range("I1:I20000"), "No")

With Range("I1:I20000")
i = 1
Set c = .Find("No", LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.Resize(1, 1).Offset(1, 0).EntireRow.Insert
Set c = .FindNext(c)
i = i + 1
Loop While Not c Is Nothing And i <= myCount
End If
End With

End Sub

 

[Fausto]

Hello

I have refined the macro a little, please try out and see if it works a little better.

Sub Test1()
Application.ScreenUpdating = False
Dim mycount As Long
Dim oldsel As String
oldsel = ActiveCell.Address
mycount = Application.CountIf(Range("I1:I20000"), "No")
Range("I1").Select
On Error resume next
Do
    Range("I1:I20000").Find(What:="No", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
    ActiveCell.Offset(1, 0).EntireRow.Insert
    i = i + 1
    If Err <> 0 Then Exit Do
    ActiveCell.Offset(1, 0).Select
Loop Until i = mycount
Range(oldsel).Activate
Application.ScreenUpdating = True
End Sub

Hope this helps

 

[colbymack]

Fausto's solution is similar to the one I just posted, although Fausto's contains some error handling, which mine does not.

Good luck with either solution!