Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert text in column A and calculate the average from column B
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........Average ......Average April April AprilData.....Average....... Average Jacob Skaria gave me this VBA who works perfectly, but only for the first blank line. CAn anybody help me with the rest ? 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert text in column A and calculate the average from column B
Sverre
The below code works without any blanks...If you review my original post I have mentioned that the code will insert a a blank line after each section and do the averages.... So please try with no blank lines in between.... If otherwise post back/.. 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........Average ......Average April April AprilData.....Average....... Average Jacob Skaria gave me this VBA who works perfectly, but only for the first blank line. CAn anybody help me with the rest ? 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert text in column A and calculate the average from column B
Hi, I have modified the code which identifies the blank rows and populate the
average.....You can have any number of blanks inbetween sections...... Please try and feedback.. Sub InsertAveragesInBlankRows() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim lngLastRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row strCurData = Range("A" & lngRow) For lngRow = 1 To lngLastRow If strCurData = "" And Range("A" & lngRow) < "" Then lngStartRow = lngRow If strCurData < Range("A" & lngRow) And strCurData < "" Then 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 strCurData = Range("A" & lngRow) If strCurData < "" Then lngStartRow = lngRow End If Next '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........Average ......Average April April AprilData.....Average....... Average Jacob Skaria gave me this VBA who works perfectly, but only for the first blank line. CAn anybody help me with the rest ? 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert text in column A and calculate the average from column
Than you very much. Now its working perfect down the sheet. I am very grateful.
Jacob Skaria skrev: Hi, I have modified the code which identifies the blank rows and populate the average.....You can have any number of blanks inbetween sections...... Please try and feedback.. Sub InsertAveragesInBlankRows() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim lngLastRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row strCurData = Range("A" & lngRow) For lngRow = 1 To lngLastRow If strCurData = "" And Range("A" & lngRow) < "" Then lngStartRow = lngRow If strCurData < Range("A" & lngRow) And strCurData < "" Then 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 strCurData = Range("A" & lngRow) If strCurData < "" Then lngStartRow = lngRow End If Next '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........Average ......Average April April AprilData.....Average....... Average Jacob Skaria gave me this VBA who works perfectly, but only for the first blank line. CAn anybody help me with the rest ? 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert text in column A and calculate the average from column
Welcome..
If this post helps click Yes --------------- Jacob Skaria "Sverre" wrote: Than you very much. Now its working perfect down the sheet. I am very grateful. Jacob Skaria skrev: Hi, I have modified the code which identifies the blank rows and populate the average.....You can have any number of blanks inbetween sections...... Please try and feedback.. Sub InsertAveragesInBlankRows() Dim lngRow As Long Dim lngCol As Long Dim lngStartRow As Long Dim lngLastRow As Long Dim strCurData As String lngRow = 1 lngStartRow = lngRow lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row strCurData = Range("A" & lngRow) For lngRow = 1 To lngLastRow If strCurData = "" And Range("A" & lngRow) < "" Then lngStartRow = lngRow If strCurData < Range("A" & lngRow) And strCurData < "" Then 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 strCurData = Range("A" & lngRow) If strCurData < "" Then lngStartRow = lngRow End If Next '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........Average ......Average April April AprilData.....Average....... Average Jacob Skaria gave me this VBA who works perfectly, but only for the first blank line. CAn anybody help me with the rest ? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate weighted average for 1 column | Excel Worksheet Functions | |||
Insert Column after specific text title the new column and add for | Excel Discussion (Misc queries) | |||
Insert a text in Column A and calculate the average from column B | Excel Programming | |||
Insert a text in column A and calculoate the average for every gro | Excel Discussion (Misc queries) | |||
calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) |