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 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 78
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8,520
Default 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
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 calculate the average from column B Sverre Excel Programming 7 April 23rd 09 12:13 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


All times are GMT +1. The time now is 12:34 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"