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
|