Macro to search a column for a specific word and replace with

[wendy]

Is possible to have a macro that look at a specific
column (A: A5000) and search for every fifth occurrence
for the word 'TOTAL" and replace the first total with
total1 (Eg A115. no reference to the cell but the word
total). and then create a blank rows of cell and enter
below that same column (A116) vacation1, (A117) sick1
etc. Then on the 10 occurrence of total replace with
total2 (Eg: A300) below that same column create a blank
rows and enter vacation2 (A301), below create a blank
rows SICK2 (A302) I am working on some agent activities
and I will prefer to use a macro since I will be running
the same data daily. I will prefer searching for the
fifth total and no reference to a particular cell number.
Thanks a lot. You guys have been extreme helpful.

Example. Macro search for 5th total on column A. when
found replace with total1. say fifth column found in
A115. if total1 is in A115 then create a blank row in
A116 and enter Vacation1 and create blank cell in A117
and enter Sick1.

Macro search for 10th total on column A. when found
replace with total1. Say 10th column found in A300 if
total2 then create a blank row in A301 and enter
vacation2.
Then create a blank row in A116 and enter Sick2. I
have recorded some macro before but I have Zero idea in
writing macro. So any help will be grateful.

 

[Ken Wright]

Try this on a backup copy of your data first:-

Sub TotalByFive()

Set Rng = ActiveSheet.Range("A:A")

c = 2
Range("A1").Select

Set found = Rng.Cells.Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)

If Not found Is Nothing Then
firstAddress = found.Address

Do
found.Activate

Set found = Cells.FindNext(after:=ActiveCell)
If found.Address = firstAddress Then
Exit Do
End If

c = c + 1
If c Mod 5 = 0 Then
With found
.Value = "Total" & (c / 5)
.Offset(1, 0).Resize(2, 1).EntireRow.Insert
.Offset(1, 0).Resize(2, 1).Value = _
WorksheetFunction.Transpose(Array("Vacation" _
& (c / 5), "Sick" & (c / 5)))
End With
End If
Loop
End If
End Sub

 

[Tom Ogilvy]

Sub ProcessData()
Dim cnt As Long, cnt1 As Long
Dim c As Range
Dim firstAddress As String
With Worksheets(1).Columns(1)
Set c = .Find("Total", LookIn:=xlValues)
If Not c Is Nothing Then
cnt = 1
firstAddress = c.Address
Do
If cnt Mod 5 = 0 Then
cnt1 = Application.Round(cnt / 5, 0)
c.Offset(1, 0).Resize(5).EntireRow.Insert
c.Value = "Total" & cnt1
c.Offset(1, 0).Value = "Vacation" & cnt1
c.Offset(2, 0).Value = "Sick" & cnt1
End If
Set c = .FindNext(c)
cnt = cnt + 1
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

 

[wendy]

Thanks both KEN and TOM. Both macros worked, except the
first total set of totals is at the 4th and every other
sets are after the 5th. Any idea why? The first set is
not the 5th occurrence of total?
You guys are perfect, I have to say it. Without you
guys, me and my friend Mary would not have been able to
do anything with this project. Tom? Any idea on any
resources that could help us? I love this macro thing,
but i cannot figure it out. TOM i am planning to call
that macro you gave my friend yesterday once this macro
has added total1 and soon.

But my problem now is how would the macro's formula
knows the range for total2, total3 and soon without
including value for total1 in total2.or include total1,
total2 for total3? I added some range for testing but
these are not the range that I will be looking for.. I
only want the total for items C1:TOTAL1 and E1:TOTAL1.
And total2 to start from total1 and whatever range C AND
E to the end of total2. And total3 to start from total3
to the end of total3. This is the macro you gave me
earlier TOM. Is it possible to make reference to total1
even though the values to calculate will be coming from C
AND E.? Is it possible to make reference to total1 for
the first calculation for range C AND E.? And for total2
only all rows after total1? BASICALLY COLUMN C AND E are
the column that will be use to calculate the different
totals.

Sub supervisor()
Dim rng As Range
If Not rng Is Nothing Then
rng.Offset(0, 1).Formula = _
"=SUMIF(c1:c111,""vacation"",e1:e111)"
rng.Offset(0, 1).BorderAround Weight:=xlMedium
End If
Set rng = Cells.Find("total1")
If Not rng Is Nothing Then
rng.Offset(0, 1).Formula = _
"=SUMIF(C1:c111,""total"",E1:e111)-SUMIF
(C1:c111,""lunch"",E1:e111)"
rng.Offset(0, 1).BorderAround Weight:=xlMedium
End If

 

[wendy]

Thanks you very much for this macro. it work well.I will
like to include Sick and other entries in you macro. how
do i add them. I have tried but no luck.

-----Original Message-----
Try this on a backup copy of your data first:-

Sub TotalByFive()

Set Rng = ActiveSheet.Range("A:A")

c = 2
Range("A1").Select

Set found = Rng.Cells.Find(what:="Total",

LookIn:=xlValues, lookat:=xlWhole, _

 

[Ken Wright]

Assuming you wanted to add 2 more rows and call them SpareA and SpareB

With found
.Value = "Total" & (c / 5)
.Offset(1, 0).Resize(2, 1).EntireRow.Insert
.Offset(1, 0).Resize(2, 1).Value = _
WorksheetFunction.Transpose(Array("Vacation" _
& (c / 5), "Sick" & (c / 5)))
End With

becomes

With found
.Value = "Total" & (c / 5)
.Offset(1, 0).Resize(4, 1).EntireRow.Insert
.Offset(1, 0).Resize(4, 1).Value = _
WorksheetFunction.Transpose(Array("Vacation" _
& (c / 5), "Sick" & (c / 5), "SpareA" & (c / 5), "SpareB" & (c /
5)))
End With

By the way - In case you are thinking the two solutions look very similar, it's
probably that the code I used was very likely cribbed from one of Tom's old
posts anyway

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Thanks you very much for this macro. it work well.I will
like to include Sick and other entries in you macro. how
do i add them. I have tried but no luck.

<snip>