View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
mary mary is offline
external usenet poster
 
Posts: 19
Default Macro to find an occurrence and create blanks rows

Hello everyone,
I have been helping Wendywith her project, but i am
a novice just as she. I was able to find this macro, but
it is not exactly what she is looking for. it only
changes the first or every total.
We would like it to change every 5th occurrences of total
total1. The 10th total to total2. etc...and after every
occurrence it creates 5 blank rows above the replace
total1. Could someone help us please?
I am embarrassed to ask TOM because he has been of extreme
help through out this project

Sub find()
Dim rFound As Range
Dim szFirst As String


ThisWorkbook.Worksheets(1).Activate
Set rFound = Columns(1).find("total")
Do While Not rFound Is Nothing
''' Store address of first occurrence
If szFirst = "" Then
szFirst = rFound.Address
ElseIf rFound.Address = szFirst Then
Exit Do ''' If we have looped around, quit
End If
rFound.Value = Application.Substitute(rFound.Value,
_
"total", "total1")
Loop

End Sub