|
Insert a text in column A and calculoate the average for every gro
I have a sheet of 20000 rows or more. I have a grouped the sheet with one
blank line. In this blank line I want to insert a text in Column A like this; content in the cell above+data. In addition I want to put in a formula calculating the average for the group above from column B to column U. CAn anyone help me with a VBA to do this. May be I have to put in two blank lines ? A B C May May May Maydata Average Average June June June Jundata |
Insert a text in column A and calculoate the average for every gro
Assuming that your data will not have any blank lines inbeween, the below
macro will insert a blank row between each group, insert a text in ColA with <RangeString & "Data" and then will insert the Average formula from ColB to Col U. Please try and feedback Sub InsertAverages() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) Do While Range("A" & lngRow) < "" If strCurData < Range("A" & lngRow) Then Rows(lngRow).Insert Range("A" & lngRow) = strCurData & " Data" 'Insert Averages from ColB to U For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next lngRow = lngRow + 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) End If lngRow = lngRow + 1 Loop 'Handle Last Range Range("A" & lngRow) = strCurData & " Data" For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sverre" wrote: I have a sheet of 20000 rows or more. I have a grouped the sheet with one blank line. In this blank line I want to insert a text in Column A like this; content in the cell above+data. In addition I want to put in a formula calculating the average for the group above from column B to column U. CAn anyone help me with a VBA to do this. May be I have to put in two blank lines ? A B C May May May Maydata Average Average June June June Jundata |
Insert a text in column A and calculoate the average for every
Thank you. There are two lines with errors: Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow -1 & "C)" Are the statments too long ? Jacob Skaria skrev: Assuming that your data will not have any blank lines inbeween, the below macro will insert a blank row between each group, insert a text in ColA with <RangeString & "Data" and then will insert the Average formula from ColB to Col U. Please try and feedback Sub InsertAverages() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) Do While Range("A" & lngRow) < "" If strCurData < Range("A" & lngRow) Then Rows(lngRow).Insert Range("A" & lngRow) = strCurData & " Data" 'Insert Averages from ColB to U For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next lngRow = lngRow + 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) End If lngRow = lngRow + 1 Loop 'Handle Last Range Range("A" & lngRow) = strCurData & " Data" For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sverre" wrote: I have a sheet of 20000 rows or more. I have a grouped the sheet with one blank line. In this blank line I want to insert a text in Column A like this; content in the cell above+data. In addition I want to put in a formula calculating the average for the group above from column B to column U. CAn anyone help me with a VBA to do this. May be I have to put in two blank lines ? A B C May May May Maydata Average Average June June June Jundata |
Insert a text in column A and calculoate the average for every
Try this..
Sub InsertAverages() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) Do While Range("A" & lngRow) < "" If strCurData < Range("A" & lngRow) Then Rows(lngRow).Insert Range("A" & lngRow) = strCurData & " Data" 'Insert Averages from ColB to U For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = _ "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next lngRow = lngRow + 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) End If lngRow = lngRow + 1 Loop 'Handle Last Range Range("A" & lngRow) = strCurData & " Data" For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = _ "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sverre" wrote: Thank you. There are two lines with errors: Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow -1 & "C)" Are the statments too long ? Jacob Skaria skrev: Assuming that your data will not have any blank lines inbeween, the below macro will insert a blank row between each group, insert a text in ColA with <RangeString & "Data" and then will insert the Average formula from ColB to Col U. Please try and feedback Sub InsertAverages() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) Do While Range("A" & lngRow) < "" If strCurData < Range("A" & lngRow) Then Rows(lngRow).Insert Range("A" & lngRow) = strCurData & " Data" 'Insert Averages from ColB to U For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next lngRow = lngRow + 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) End If lngRow = lngRow + 1 Loop 'Handle Last Range Range("A" & lngRow) = strCurData & " Data" For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sverre" wrote: I have a sheet of 20000 rows or more. I have a grouped the sheet with one blank line. In this blank line I want to insert a text in Column A like this; content in the cell above+data. In addition I want to put in a formula calculating the average for the group above from column B to column U. CAn anyone help me with a VBA to do this. May be I have to put in two blank lines ? A B C May May May Maydata Average Average June June June Jundata |
Insert a text in column A and calculoate the average for every
It works perfectly-Thank you very much !!!!!!!!!!!
Jacob Skaria skrev: Try this.. Sub InsertAverages() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) Do While Range("A" & lngRow) < "" If strCurData < Range("A" & lngRow) Then Rows(lngRow).Insert Range("A" & lngRow) = strCurData & " Data" 'Insert Averages from ColB to U For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = _ "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next lngRow = lngRow + 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) End If lngRow = lngRow + 1 Loop 'Handle Last Range Range("A" & lngRow) = strCurData & " Data" For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = _ "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sverre" wrote: Thank you. There are two lines with errors: Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow -1 & "C)" Are the statments too long ? Jacob Skaria skrev: Assuming that your data will not have any blank lines inbeween, the below macro will insert a blank row between each group, insert a text in ColA with <RangeString & "Data" and then will insert the Average formula from ColB to Col U. Please try and feedback Sub InsertAverages() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) Do While Range("A" & lngRow) < "" If strCurData < Range("A" & lngRow) Then Rows(lngRow).Insert Range("A" & lngRow) = strCurData & " Data" 'Insert Averages from ColB to U For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next lngRow = lngRow + 1 lngStartRow = lngRow strCurData = Range("A" & lngRow) End If lngRow = lngRow + 1 Loop 'Handle Last Range Range("A" & lngRow) = strCurData & " Data" For lngCol = 2 To 21 Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" & lngRow - 1 & "C)" Next End Sub If this post helps click Yes --------------- Jacob Skaria "Sverre" wrote: I have a sheet of 20000 rows or more. I have a grouped the sheet with one blank line. In this blank line I want to insert a text in Column A like this; content in the cell above+data. In addition I want to put in a formula calculating the average for the group above from column B to column U. CAn anyone help me with a VBA to do this. May be I have to put in two blank lines ? A B C May May May Maydata Average Average June June June Jundata |
| All times are GMT +1. The time now is 11:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com