| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine two macros using InStr?
Hi All.........
I got a macro from here the other day, (sorry, don't remember who submitted it), and modified it for my own use. Unfortunately, in order to get all I want done, I had to turn it into two seperate macros, as shown below. Can anyone please tell me how these two might be combined into just one macro?.........or maybe it's not possible? The only thing I've changed between the two, is the 8 to a 3 in the InStr statement, to get the thing to read for "Total" in column 3 instead of column 8. It works fine this way, just aggravating to have to have two........ ==================================== Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ==================================== Sub AddRowSubTotalsInternational() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ===================================== Vaya con Dios, Chuck, CABGx3 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine two macros using InStr?
Sub AddRowSubTotalsBoth()
lastrow = Range("A" & Rows.Count).End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "CLR" wrote in message ... Hi All......... I got a macro from here the other day, (sorry, don't remember who submitted it), and modified it for my own use. Unfortunately, in order to get all I want done, I had to turn it into two seperate macros, as shown below. Can anyone please tell me how these two might be combined into just one macro?.........or maybe it's not possible? The only thing I've changed between the two, is the 8 to a 3 in the InStr statement, to get the thing to read for "Total" in column 3 instead of column 8. It works fine this way, just aggravating to have to have two........ ==================================== Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ==================================== Sub AddRowSubTotalsInternational() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ===================================== Vaya con Dios, Chuck, CABGx3 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine two macros using InStr?
Hi
try Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 or InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany CLR wrote: Hi All......... I got a macro from here the other day, (sorry, don't remember who submitted it), and modified it for my own use. Unfortunately, in order to get all I want done, I had to turn it into two seperate macros, as shown below. Can anyone please tell me how these two might be combined into just one macro?.........or maybe it's not possible? The only thing I've changed between the two, is the 8 to a 3 in the InStr statement, to get the thing to read for "Total" in column 3 instead of column 8. It works fine this way, just aggravating to have to have two........ ==================================== Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ==================================== Sub AddRowSubTotalsInternational() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ===================================== Vaya con Dios, Chuck, CABGx3 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine two macros using InStr?
You macro is flawed. Lastrow does not adjust as you add rows, so if you
have any total near the end of your range, you would miss it. You should loop from lastrow to 8 Sub AddRowSubTotalsBoth() lastrow = Range("A" & Rows.Count).End(xlUp).Row For r = lastrow to 8 step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All......... I got a macro from here the other day, (sorry, don't remember who submitted it), and modified it for my own use. Unfortunately, in order to get all I want done, I had to turn it into two seperate macros, as shown below. Can anyone please tell me how these two might be combined into just one macro?.........or maybe it's not possible? The only thing I've changed between the two, is the 8 to a 3 in the InStr statement, to get the thing to read for "Total" in column 3 instead of column 8. It works fine this way, just aggravating to have to have two........ ==================================== Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ==================================== Sub AddRowSubTotalsInternational() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ===================================== Vaya con Dios, Chuck, CABGx3 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine two macros using InStr?
Thanks Tom...........
Of course I haven't a clue about what you're talking about..........but if you say so, it's gospel. I'll make your recommended changes, and thanks again. Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote in message ... You macro is flawed. Lastrow does not adjust as you add rows, so if you have any total near the end of your range, you would miss it. You should loop from lastrow to 8 Sub AddRowSubTotalsBoth() lastrow = Range("A" & Rows.Count).End(xlUp).Row For r = lastrow to 8 step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All......... I got a macro from here the other day, (sorry, don't remember who submitted it), and modified it for my own use. Unfortunately, in order to get all I want done, I had to turn it into two seperate macros, as shown below. Can anyone please tell me how these two might be combined into just one macro?.........or maybe it's not possible? The only thing I've changed between the two, is the 8 to a 3 in the InStr statement, to get the thing to read for "Total" in column 3 instead of column 8. It works fine this way, just aggravating to have to have two........ ==================================== Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ==================================== Sub AddRowSubTotalsInternational() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ===================================== Vaya con Dios, Chuck, CABGx3 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine two macros using InStr?
Thanks Tom...........
Of course I haven't a clue about what you're talking about..........but if you say so, it's gospel. I'll make your recommended changes, and thanks again. Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote in message ... You macro is flawed. Lastrow does not adjust as you add rows, so if you have any total near the end of your range, you would miss it. You should loop from lastrow to 8 Sub AddRowSubTotalsBoth() lastrow = Range("A" & Rows.Count).End(xlUp).Row For r = lastrow to 8 step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All......... I got a macro from here the other day, (sorry, don't remember who submitted it), and modified it for my own use. Unfortunately, in order to get all I want done, I had to turn it into two seperate macros, as shown below. Can anyone please tell me how these two might be combined into just one macro?.........or maybe it's not possible? The only thing I've changed between the two, is the 8 to a 3 in the InStr statement, to get the thing to read for "Total" in column 3 instead of column 8. It works fine this way, just aggravating to have to have two........ ==================================== Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ==================================== Sub AddRowSubTotalsInternational() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ===================================== Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote in message ... You macro is flawed. Lastrow does not adjust as you add rows, so if you have any total near the end of your range, you would miss it. You should loop from lastrow to 8 Sub AddRowSubTotalsBoth() lastrow = Range("A" & Rows.Count).End(xlUp).Row For r = lastrow to 8 step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All......... I got a macro from here the other day, (sorry, don't remember who submitted it), and modified it for my own use. Unfortunately, in order to get all I want done, I had to turn it into two seperate macros, as shown below. Can anyone please tell me how these two might be combined into just one macro?.........or maybe it's not possible? The only thing I've changed between the two, is the 8 to a 3 in the InStr statement, to get the thing to read for "Total" in column 3 instead of column 8. It works fine this way, just aggravating to have to have two........ ==================================== Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ==================================== Sub AddRowSubTotalsInternational() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ===================================== Vaya con Dios, Chuck, CABGx3 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine two macros using InStr?
Thank you Don and Bob, and Frank...........that was exactly what I was
looking for Vaya con Dios Chuck, CABGx3 "CLR" wrote in message ... Hi All......... I got a macro from here the other day, (sorry, don't remember who submitted it), and modified it for my own use. Unfortunately, in order to get all I want done, I had to turn it into two seperate macros, as shown below. Can anyone please tell me how these two might be combined into just one macro?.........or maybe it's not possible? The only thing I've changed between the two, is the 8 to a 3 in the InStr statement, to get the thing to read for "Total" in column 3 instead of column 8. It works fine this way, just aggravating to have to have two........ ==================================== Sub AddRowSubTotalsDomestic() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 8).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ==================================== Sub AddRowSubTotalsInternational() lastrow = Range("A65536").End(xlUp).Row For r = 8 To lastrow If InStr(1, Cells(r, 3).Value, "Total") 0 Then ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub ===================================== Vaya con Dios, Chuck, CABGx3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine macros variable help | Excel Worksheet Functions | |||
| combine two macros | Excel Discussion (Misc queries) | |||
| How do I combine MACROS and functions? | New Users to Excel | |||
| combine two macros | Excel Worksheet Functions | |||
| Combine 2 macros | Excel Programming | |||
Hybrid Mode
