Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 78
Default Insert a 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8,520
Default Insert a text in Column A and calculate the average from column B

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 735
Default Insert a text in Column A and calculate the average from column B

Will insert formula into empty row - based on empty column A.

Sub SetAverage()
Dim lfR As Long, llR As Long
Dim lcR As Long

On Error GoTo errHandler
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
lfR = 1
For lcR = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row + 1
If Len(Trim(.Cells(lcR, 1))) = 0 Then
.Cells(lcR, 1) = .Cells(lcR - 1, 1) & "- Averages"
.Cells(lcR, 2).Formula = "=AVERAGE(B" & lfR & ":B" & lcR - 1 & ")"
.Cells(lcR, 2).Copy Destination:=.Range(.Cells(lcR, 3), .Cells(lcR,
21))
lfR = lcR + 1
End If
Next
End With

errHandler:
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

--

Regards,
Nigel




"Sverre" wrote in message
...
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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 78
Default Insert a text in Column A and calculate the average from colum

Thanks Jacob. I got a massage Compile error in theese statements:
Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" &
lngRow -1 & "C)"
Is the statement to long ? I traied to put a _ afrer & but it do not help.


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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8,520
Default Insert a text in Column A and calculate the average from colum

Please 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:

Thanks Jacob. I got a massage Compile error in theese statements:
Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" &
lngRow -1 & "C)"
Is the statement to long ? I traied to put a _ afrer & but it do not help.


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



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 78
Default Insert a text in Column A and calculate the average from colum

Thank you very very much, it works perfectly. Its a good and happy day to day.
Sverre

Jacob Skaria skrev:

Please 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:

Thanks Jacob. I got a massage Compile error in theese statements:
Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" &
lngRow -1 & "C)"
Is the statement to long ? I traied to put a _ afrer & but it do not help.


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

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 78
Default Insert a text in Column A and calculate the average from colum

Thank you Nigel.
Thank you. This works too. Its a useful program. wich I have stored in my
collection.

Nigel skrev:

Will insert formula into empty row - based on empty column A.

Sub SetAverage()
Dim lfR As Long, llR As Long
Dim lcR As Long

On Error GoTo errHandler
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
lfR = 1
For lcR = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row + 1
If Len(Trim(.Cells(lcR, 1))) = 0 Then
.Cells(lcR, 1) = .Cells(lcR - 1, 1) & "- Averages"
.Cells(lcR, 2).Formula = "=AVERAGE(B" & lfR & ":B" & lcR - 1 & ")"
.Cells(lcR, 2).Copy Destination:=.Range(.Cells(lcR, 3), .Cells(lcR,
21))
lfR = lcR + 1
End If
Next
End With

errHandler:
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

--

Regards,
Nigel




"Sverre" wrote in message
...
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



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 78
Default Insert a text in Column A and calculate the average from colum

At a closer look, it works only for the first blank line, not at the 2. and
3. and so on.

Sverre skrev:

Thank you very very much, it works perfectly. Its a good and happy day to day.
Sverre

Jacob Skaria skrev:

Please 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:

Thanks Jacob. I got a massage Compile error in theese statements:
Cells(lngRow, lngCol).FormulaR1C1 = "=Average(R" & lngStartRow & "C:R" &
lngRow -1 & "C)"
Is the statement to long ? I traied to put a _ afrer & but it do not help.


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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate weighted average for 1 column Jul2010 Excel Worksheet Functions 3 January 21st 10 05:29 PM
Insert Column after specific text title the new column and add for David Excel Discussion (Misc queries) 5 October 2nd 09 04:03 PM
Insert a text in column A and calculoate the average for every gro Sverre Excel Discussion (Misc queries) 4 April 23rd 09 11:50 AM
calculate average in a column based on criteria in another column sharon t Excel Discussion (Misc queries) 2 May 12th 06 06:07 PM
calculate the average of a column then move it to excel Aster99 Excel Programming 1 April 26th 04 11:39 AM


All times are GMT +1. The time now is 08:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"