TO TOM OR KEN: macro to calculate selected range
 

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

-----Original Message-----
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

TO TOM OR KEN: macro to calculate selected range
 

I couldn't reproduce that behavior - This modification should be more robust
and shows you how to put your formulas in (easier to do it at the same
time). I can't tell from the sample macro you show what the exact formula
is because you seem to be putting in one formula, then overwriting it with
another in your sample macro.

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

I probably won't be around for a while, so perhaps you want to work with Ken
or just post your follow on questions to the forum.

--
Regards,
Tom Ogilvy
"mary" wrote in message
...
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

-----Original Message-----
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

TO TOM OR KEN: macro to calculate selected range
 

you might want to change

Set rngStart = c.Offset(1, 0)


to

Set rngStart = c.Offset(6, 0)

although I don't think it will actually affect anything the way it is.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
I couldn't reproduce that behavior - This modification should be more
robust
and shows you how to put your formulas in (easier to do it at the same
time). I can't tell from the sample macro you show what the exact formula
is because you seem to be putting in one formula, then overwriting it with
another in your sample macro.

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

I probably won't be around for a while, so perhaps you want to work with
Ken
or just post your follow on questions to the forum.

--
Regards,
Tom Ogilvy
"mary" wrote in message
...
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

-----Original Message-----
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

TO TOM OR KEN: macro to calculate selected range
 

Hello Tom,
thanks a million. I am getting syntax error and it is
pointing to:

& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"


and
& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"
Any idea. Thanks again
-----Original Message-----
you might want to change

Set rngStart = c.Offset(1, 0)


to

Set rngStart = c.Offset(6, 0)

although I don't think it will actually affect anything
the way it is.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
I couldn't reproduce that behavior - This modification
should be more
robust
and shows you how to put your formulas in (easier to
do it at the same
time). I can't tell from the sample macro you show
what the exact formula
is because you seem to be putting in one formula, then
overwriting it with
another in your sample macro.

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

I probably won't be around for a while, so perhaps you
want to work with
Ken
or just post your follow on questions to the forum.

--
Regards,
Tom Ogilvy
"mary" wrote in message
...
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

-----Original Message-----
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





.