| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert cell text every 5 rows in a column | Excel Discussion (Misc queries) | |||
| Request for VB to do something simple (insert text in a column ofcells) | Excel Discussion (Misc queries) | |||
| when the column text is different, how to insert one row below eac | Excel Worksheet Functions | |||
| How to insert text in one column and populate numbers in another | Excel Worksheet Functions | |||
| how insert same text in empty cells in column (10000 rows) | Excel Worksheet Functions | |||
Linear Mode
