Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to find an occurrence and create blanks rows
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 -- Regards, Tom Ogilvy mary wrote in 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
tTO TOM OR KEN: Macro to find an occurrence and create blanks rows
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
tTO TOM OR KEN: Macro to find an occurrence and create blanks rows
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 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).Addresss & ")" c.offset(1,1).BordersAround Weight:=xlMedium c.Offset(2,1).Formula = "=Sumif(" & rng1.offset(0,2).Address & _ ",Sick," & rng1.offset(0,4).Addresss & ")" c.offset(2,1).BordersAround 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 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to fill in rows with blanks | Excel Discussion (Misc queries) | |||
Find Last Occurrence of Number | Excel Discussion (Misc queries) | |||
How to find SECOND occurrence in a string? | Excel Worksheet Functions | |||
Find nth occurrence and replace with ":" | Excel Worksheet Functions | |||
find last occurrence | Excel Worksheet Functions |