Macro To Insert Row And Add Data.

[quinla01]

A forum user (GER) kindly wrote this macro for me yesterday to find the
cell that holds the text "homeDirectory:" , insert a row above the cell
and insert the text "replace: homeDirectory" into the new row (in the
first column).

I was hoping someone could alter this code for me to produce a new
macro which finds any cells that hold the text "homeDirectory:
\\server1" and inserts a row below the cell and inserts a hyphen "-" in
the first column of the new row.

The data is from an active directory export and only populates 1
column, however the column has 5000 rows.

=================================

Public Sub test()

Dim C As Variant
Dim FirstRow As Integer

With Worksheets(1).Range("a1:a5500")
Set C = .Find("homeDirectory:", LookIn:=xlValues)
If Not C Is Nothing Then
FirstRow = C.Row + 1
Do
C.Insert Shift:=xlDown
C.Offset(-1, 0).Value = "replace: homeDirectory"
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Row <> FirstRow
End If
End With


End Sub

======================


Thanks for any help

Andy

 

[Tom Ogilvy]

Public Sub test1()

Dim C As Variant
Dim FirstRow As Integer

With Worksheets(1).Range("a1:a5500")
Set C = .Find("homeDirectory: \\server1", After:=Range("A65536"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If Not C Is Nothing Then
FirstRow = C.Row
Do
C.Offset(1,0).EntireRow.Insert
Cells(c.row + 1,1).Value = "-"
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Row <> FirstRow
End If
End With
End Sub