J
jane
This code was given to me and it works well. I have added
about 7 more items that i want to see the sumif value for.
It suppose to look of the 5 total and create total1,
sick1, vacation1 and sumif the value, then the next 5
occurence
of total and create total2, sick2 and vacation2 and
sumif the value and so on.
The problem i am facing it that the sumif produce the
correct calculation up to total5, sick5 and vacation5. At
total6
, sick6 and vacation6 the sumif does not sumif cells
sick5 and vacation5 to sick6 and vacation6. some how
it is caclulating part of range sick5, vacation5 to
sick6, vacation6. I have made adjustment
to the cnt/5 and that did not fix it. Now mod 5 relates
to the 5th occurrence which it fine.
I have spend nights and nights trying to modify this
macro with no luck. I will appreciate any help.
TOM and KEN are familiar with this code. Thanks in advance
Sub ProcessData()
Dim cnt As Long, cnt1 As Long
Dim c As Range
Dim firstAddress As String
Dim rngStart As Range
With Worksheets(1).Columns(1)
Set rngStart = .Cells(1, 1)
Set c = .Find("Total", _
After:=Worksheets(1).Cells(Rows.Count, 1), _
Lookat:=xlPart, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
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
Set rng1 = Worksheets(1).Range(rngStart,
c.Offset(-1, 0))
c.Offset(1, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"
c.Offset(1, 1).BorderAround Weight:=xlMedium
c.Offset(2, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"
c.Offset(2, 1).BorderAround Weight:=xlMedium
Set rngStart = c.Offset(1, 0)
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
about 7 more items that i want to see the sumif value for.
It suppose to look of the 5 total and create total1,
sick1, vacation1 and sumif the value, then the next 5
occurence
of total and create total2, sick2 and vacation2 and
sumif the value and so on.
The problem i am facing it that the sumif produce the
correct calculation up to total5, sick5 and vacation5. At
total6
, sick6 and vacation6 the sumif does not sumif cells
sick5 and vacation5 to sick6 and vacation6. some how
it is caclulating part of range sick5, vacation5 to
sick6, vacation6. I have made adjustment
to the cnt/5 and that did not fix it. Now mod 5 relates
to the 5th occurrence which it fine.
I have spend nights and nights trying to modify this
macro with no luck. I will appreciate any help.
TOM and KEN are familiar with this code. Thanks in advance
Sub ProcessData()
Dim cnt As Long, cnt1 As Long
Dim c As Range
Dim firstAddress As String
Dim rngStart As Range
With Worksheets(1).Columns(1)
Set rngStart = .Cells(1, 1)
Set c = .Find("Total", _
After:=Worksheets(1).Cells(Rows.Count, 1), _
Lookat:=xlPart, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
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
Set rng1 = Worksheets(1).Range(rngStart,
c.Offset(-1, 0))
c.Offset(1, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"
c.Offset(1, 1).BorderAround Weight:=xlMedium
c.Offset(2, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"
c.Offset(2, 1).BorderAround Weight:=xlMedium
Set rngStart = c.Offset(1, 0)
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