| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bold & add line after subtotal row
I searched messages and found the following code by Chuck from 10/6/06 and
would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Pam,
If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Thanks, Bernie, for the explanation. This will help me to apply this code to
my projects. "Bernie Deitrick" wrote: Pam, If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Bernie,
I may have spoke too soon. I did get it to work for the first part of my spreadsheet, but can you tell me how to get it to work for a named range (just one section at a time)? Thanks again for your help. Pam "Bernie Deitrick" wrote: Pam, If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Pam,
You need to post your code, explain what your named range is (what range it refers to), and what you want to do - a bit more explanation, in short... HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, I may have spoke too soon. I did get it to work for the first part of my spreadsheet, but can you tell me how to get it to work for a named range (just one section at a time)? Thanks again for your help. Pam "Bernie Deitrick" wrote: Pam, If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Bernie,
Sorry about that. I have a spreadsheet starting with column A contains Tech, JobNumber, Type, Code, StartTime, StopTime, and CalculatedTime. I have it sorted by Tech. I ran subtotal for each change in JobNumber (for each Tech - I'm working each section separately because there are other calculations needed in each Tech grouping) to sum CalculatedTime. I wanted to bold subtotal row and add a row after. The explanation and tweaking you provided earlier worked great for the first Tech when I only had one set of subtotals. I moved to the second Tech section and ran code and it inserted another row under each subtotal for the first Tech. So I thought I would name each section. Currently working with "DarrylJobs" (A218:G491). Column C has the word total in it for each subtotal. Here is the code I'm using: Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 7).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True Range ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Thanks, Pam "Bernie Deitrick" wrote: Pam, You need to post your code, explain what your named range is (what range it refers to), and what you want to do - a bit more explanation, in short... HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, I may have spoke too soon. I did get it to work for the first part of my spreadsheet, but can you tell me how to get it to work for a named range (just one section at a time)? Thanks again for your help. Pam "Bernie Deitrick" wrote: Pam, If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Pam,
Change this If InStr(1, Cells(r, 7).Value, "Total") 0 Then to If InStr(1, Cells(r, 3).Value, "Total") 0 Then 3 is column C - the syntax of the Cells() range object is Cells(Row#, Colum#), and it indicates a single cell. If you want to have code that finds Total in any column, then use something like Sub AddRowSubTotalsAssignedTo2() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.WorksheetFunction.CountIf(Rows(r), "*Total*") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next r End Sub HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, Sorry about that. I have a spreadsheet starting with column A contains Tech, JobNumber, Type, Code, StartTime, StopTime, and CalculatedTime. I have it sorted by Tech. I ran subtotal for each change in JobNumber (for each Tech - I'm working each section separately because there are other calculations needed in each Tech grouping) to sum CalculatedTime. I wanted to bold subtotal row and add a row after. The explanation and tweaking you provided earlier worked great for the first Tech when I only had one set of subtotals. I moved to the second Tech section and ran code and it inserted another row under each subtotal for the first Tech. So I thought I would name each section. Currently working with "DarrylJobs" (A218:G491). Column C has the word total in it for each subtotal. Here is the code I'm using: Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 7).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True Range ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Thanks, Pam "Bernie Deitrick" wrote: Pam, You need to post your code, explain what your named range is (what range it refers to), and what you want to do - a bit more explanation, in short... HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, I may have spoke too soon. I did get it to work for the first part of my spreadsheet, but can you tell me how to get it to work for a named range (just one section at a time)? Thanks again for your help. Pam "Bernie Deitrick" wrote: Pam, If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Bernie,
I really appreciate you helping me with this. I don't think I explained myself well in the last post. I was able to figure out with your previous explanation how to change the code to the column with "Total" in it. What I need now is to know how to limit the code to a portion of the spreadsheet to add rows and bold. Like section a150:g400. Does this make sense? I hope I'm explaining it right. I've included new code I've tried, but get error "Type Mismatch". I hope you won't mind taking a look to see what is wrong? Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If InStr(1, Cells("d197:d209").Value, "Total") 0 Then Range(Cells("a197:a209"), Cells("g197:g209")).Font.Bold = True Range(Cells("a197:g209")).Rows(r + 1).EntireRow.Insert End If Next End Sub Thanks again, Pam "Bernie Deitrick" wrote: Pam, Change this If InStr(1, Cells(r, 7).Value, "Total") 0 Then to If InStr(1, Cells(r, 3).Value, "Total") 0 Then 3 is column C - the syntax of the Cells() range object is Cells(Row#, Colum#), and it indicates a single cell. If you want to have code that finds Total in any column, then use something like Sub AddRowSubTotalsAssignedTo2() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.WorksheetFunction.CountIf(Rows(r), "*Total*") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next r End Sub HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, Sorry about that. I have a spreadsheet starting with column A contains Tech, JobNumber, Type, Code, StartTime, StopTime, and CalculatedTime. I have it sorted by Tech. I ran subtotal for each change in JobNumber (for each Tech - I'm working each section separately because there are other calculations needed in each Tech grouping) to sum CalculatedTime. I wanted to bold subtotal row and add a row after. The explanation and tweaking you provided earlier worked great for the first Tech when I only had one set of subtotals. I moved to the second Tech section and ran code and it inserted another row under each subtotal for the first Tech. So I thought I would name each section. Currently working with "DarrylJobs" (A218:G491). Column C has the word total in it for each subtotal. Here is the code I'm using: Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 7).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True Range ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Thanks, Pam "Bernie Deitrick" wrote: Pam, You need to post your code, explain what your named range is (what range it refers to), and what you want to do - a bit more explanation, in short... HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, I may have spoke too soon. I did get it to work for the first part of my spreadsheet, but can you tell me how to get it to work for a named range (just one section at a time)? Thanks again for your help. Pam "Bernie Deitrick" wrote: Pam, If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Sub AddRowSubTotalsAssignedTo()
Dim r As Long Dim LastRow As Long Dim FirstRow As Long Dim myR As Range Set myR = Range("A150:G400") LastRow = myR.Cells(myR.Cells.Count).Row FirstRow = myR.Cells(1).Row For r = LastRow To FirstRow Step -1 If InStr(1, Cells(r, 4).Value, "Total") 0 Then Range(Cells(r,1), Cells(r,7)).Font.Bold = True Cells(r+1,1).EntireRow.Insert End If Next End Sub -- HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, I really appreciate you helping me with this. I don't think I explained myself well in the last post. I was able to figure out with your previous explanation how to change the code to the column with "Total" in it. What I need now is to know how to limit the code to a portion of the spreadsheet to add rows and bold. Like section a150:g400. Does this make sense? I hope I'm explaining it right. I've included new code I've tried, but get error "Type Mismatch". I hope you won't mind taking a look to see what is wrong? Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If InStr(1, Cells("d197:d209").Value, "Total") 0 Then Range(Cells("a197:a209"), Cells("g197:g209")).Font.Bold = True Range(Cells("a197:g209")).Rows(r + 1).EntireRow.Insert End If Next End Sub Thanks again, Pam "Bernie Deitrick" wrote: Pam, Change this If InStr(1, Cells(r, 7).Value, "Total") 0 Then to If InStr(1, Cells(r, 3).Value, "Total") 0 Then 3 is column C - the syntax of the Cells() range object is Cells(Row#, Colum#), and it indicates a single cell. If you want to have code that finds Total in any column, then use something like Sub AddRowSubTotalsAssignedTo2() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.WorksheetFunction.CountIf(Rows(r), "*Total*") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next r End Sub HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, Sorry about that. I have a spreadsheet starting with column A contains Tech, JobNumber, Type, Code, StartTime, StopTime, and CalculatedTime. I have it sorted by Tech. I ran subtotal for each change in JobNumber (for each Tech - I'm working each section separately because there are other calculations needed in each Tech grouping) to sum CalculatedTime. I wanted to bold subtotal row and add a row after. The explanation and tweaking you provided earlier worked great for the first Tech when I only had one set of subtotals. I moved to the second Tech section and ran code and it inserted another row under each subtotal for the first Tech. So I thought I would name each section. Currently working with "DarrylJobs" (A218:G491). Column C has the word total in it for each subtotal. Here is the code I'm using: Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 7).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True Range ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Thanks, Pam "Bernie Deitrick" wrote: Pam, You need to post your code, explain what your named range is (what range it refers to), and what you want to do - a bit more explanation, in short... HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, I may have spoke too soon. I did get it to work for the first part of my spreadsheet, but can you tell me how to get it to work for a named range (just one section at a time)? Thanks again for your help. Pam "Bernie Deitrick" wrote: Pam, If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Bold & add line after subtotal row
Bernie,
It worked perfect!! Thank you so much for your time and help. Greatly appreciated!! Pam P.S. Are there any books or sites you can recommend to better understand aspects of coding? Seems there is a lot more you can do if using code. "Bernie Deitrick" wrote: Sub AddRowSubTotalsAssignedTo() Dim r As Long Dim LastRow As Long Dim FirstRow As Long Dim myR As Range Set myR = Range("A150:G400") LastRow = myR.Cells(myR.Cells.Count).Row FirstRow = myR.Cells(1).Row For r = LastRow To FirstRow Step -1 If InStr(1, Cells(r, 4).Value, "Total") 0 Then Range(Cells(r,1), Cells(r,7)).Font.Bold = True Cells(r+1,1).EntireRow.Insert End If Next End Sub -- HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, I really appreciate you helping me with this. I don't think I explained myself well in the last post. I was able to figure out with your previous explanation how to change the code to the column with "Total" in it. What I need now is to know how to limit the code to a portion of the spreadsheet to add rows and bold. Like section a150:g400. Does this make sense? I hope I'm explaining it right. I've included new code I've tried, but get error "Type Mismatch". I hope you won't mind taking a look to see what is wrong? Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If InStr(1, Cells("d197:d209").Value, "Total") 0 Then Range(Cells("a197:a209"), Cells("g197:g209")).Font.Bold = True Range(Cells("a197:g209")).Rows(r + 1).EntireRow.Insert End If Next End Sub Thanks again, Pam "Bernie Deitrick" wrote: Pam, Change this If InStr(1, Cells(r, 7).Value, "Total") 0 Then to If InStr(1, Cells(r, 3).Value, "Total") 0 Then 3 is column C - the syntax of the Cells() range object is Cells(Row#, Colum#), and it indicates a single cell. If you want to have code that finds Total in any column, then use something like Sub AddRowSubTotalsAssignedTo2() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.WorksheetFunction.CountIf(Rows(r), "*Total*") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next r End Sub HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, Sorry about that. I have a spreadsheet starting with column A contains Tech, JobNumber, Type, Code, StartTime, StopTime, and CalculatedTime. I have it sorted by Tech. I ran subtotal for each change in JobNumber (for each Tech - I'm working each section separately because there are other calculations needed in each Tech grouping) to sum CalculatedTime. I wanted to bold subtotal row and add a row after. The explanation and tweaking you provided earlier worked great for the first Tech when I only had one set of subtotals. I moved to the second Tech section and ran code and it inserted another row under each subtotal for the first Tech. So I thought I would name each section. Currently working with "DarrylJobs" (A218:G491). Column C has the word total in it for each subtotal. Here is the code I'm using: Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("D" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 7).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 7)).Font.Bold = True Range ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Thanks, Pam "Bernie Deitrick" wrote: Pam, You need to post your code, explain what your named range is (what range it refers to), and what you want to do - a bit more explanation, in short... HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... Bernie, I may have spoke too soon. I did get it to work for the first part of my spreadsheet, but can you tell me how to get it to work for a named range (just one section at a time)? Thanks again for your help. Pam "Bernie Deitrick" wrote: Pam, If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True This code looks for the word Total in column C (3rd column), column H (8th column), or column L (12th column), and then bolds any row with Total any of those columns from column A to column AD. You would probably want If InStr(1, Cells(r, 7).Value, "Total") 0 Then Cells(r, 1).EntireRow.Font.Bold = True End If which will look for the word Total in column G, and bold the entire row when that is true. HTH, Bernie MS Excel MVP "PHisaw" wrote in message ... I searched messages and found the following code by Chuck from 10/6/06 and would like to adapt it to my spreadsheet, but can't get it to work. I changed lastrow = Range ("L" & Rows.Count).End(xlUP).Row from "L" to "G" as this is the column where my totals are. Will someone please explain this code, especially the InStr lines? Hopefully, if I understand it better maybe I can figure out what I'm doing wrong the reason it won't work. Sub AddRowSubTotalsAssignedTo() Dim lastrow As Long Dim r As Long lastrow = Range("L" & Rows.Count).End(xlUp).Row '"L" is the column that 'contains the GrandTotal For r = lastrow To 2 Step -1 If InStr(1, Cells(r, 3).Value, "Total") 0 Or _ InStr(1, Cells(r, 8).Value, "Total") 0 Or _ InStr(1, Cells(r, 12).Value, "Total") 0 Then Range(Cells(r, 1), Cells(r, 30)).Font.Bold = True '30 is number 'of columns from "A" that the macro will BOLD ActiveSheet.Rows(r + 1).EntireRow.Insert End If Next End Sub Any help is greatly appreicated! Thanks, Pam |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| would like each subtotal line highlighted | Excel Discussion (Misc queries) | |||
| Excel subtotal function- put subtotals in bold text | Excel Discussion (Misc queries) | |||
| excel subtotal funtion how do you get the result in bold type? | Excel Worksheet Functions | |||
| How do you add a blank line automatically after the Subtotal line | Excel Worksheet Functions | |||
| Subtotal line selection | Excel Discussion (Misc queries) | |||
Linear Mode
