Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a blank line and put in a formula
Is it posible automaticly to fond a blank line an put in a formula to
calculate the average values above up to the next blank row ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a blank line and put in a formula
Change your column mc="f" to yours
This looks for the last cell in mc and then finds the 1st blank above and then finds the next blank and places the average value in the bottom blank cell. This case is 2.5 below the 2 6 1 3 2 1 Sub averageaboveblank() mc = "f" lr = Cells(Rows.Count, mc).End(xlUp).Row MsgBox lr For i = lr To 2 Step -1 If Cells(i, mc) = "" Then br = i Exit For End If Next i MsgBox br nextup = Cells(br - 1, mc).End(xlUp).Row MsgBox nextup Cells(br, mc) = _ Application.Average(Range(Cells(nextup, mc), Cells(br, mc))) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Sverre" wrote in message ... Is it posible automaticly to fond a blank line an put in a formula to calculate the average values above up to the next blank row ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a blank line and put in a formula
Sverre skrev: Is it posible automaticly to fond a blank line an put in a formula to calculate the average values above up to the next blank row ? Thanks very musch. Im going to try it now |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a blank line and put in a formula
This will calculate multiple averages, at each blank. If you just have one,
that will work too: Sub PutInAverage() Dim myA As Range For Each myA In Selection.SpecialCells(xlCellTypeConstants, 23).Areas myA.Cells(myA.Rows.count + 1, 1).Resize(1, myA.Columns.count).Formula = _ "=Average(" & myA.Columns(1).Address(False, False) & ")" Next myA End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sverre" wrote: Is it posible automaticly to fond a blank line an put in a formula to calculate the average values above up to the next blank row ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a blank line and put in a formula
Thank you, this was perfect, but if i want to start the calculation from
column. G ? ryguy7272 skrev: This will calculate multiple averages, at each blank. If you just have one, that will work too: Sub PutInAverage() Dim myA As Range For Each myA In Selection.SpecialCells(xlCellTypeConstants, 23).Areas myA.Cells(myA.Rows.count + 1, 1).Resize(1, myA.Columns.count).Formula = _ "=Average(" & myA.Columns(1).Address(False, False) & ")" Next myA End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sverre" wrote: Is it posible automaticly to fond a blank line an put in a formula to calculate the average values above up to the next blank row ? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a blank line and put in a formula
Good question. That last amcro looked for a used range, so that may not work
as well for you, as the one below, if you have a specific column that you want to find the avewrage for. Try this: Sub subaveragetest() 'place a subtotal in column(V) wherever column(U) has a blank cell Dim lRow As Long Dim cell As Range Dim RowCount As Long Application.Calculation = xlCalculationManual Application.ScreenUpdating = False lRow = Range("G65536").End(xlUp).Row + 1 RowCount = 0 For Each cell In Range("G2:G" & lRow) If IsEmpty(cell) Then cell.Offset(0, 1).FormulaR1C1 = "=Average(R[" & -RowCount & "]C[-1]:R[-1]C[-1])" RowCount = 0 Else The logic should kind of make sense, right. Look at the Gs in the code. I think you can figure it out!! ;) HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sverre" wrote: Thank you, this was perfect, but if i want to start the calculation from column. G ? ryguy7272 skrev: This will calculate multiple averages, at each blank. If you just have one, that will work too: Sub PutInAverage() Dim myA As Range For Each myA In Selection.SpecialCells(xlCellTypeConstants, 23).Areas myA.Cells(myA.Rows.count + 1, 1).Resize(1, myA.Columns.count).Formula = _ "=Average(" & myA.Columns(1).Address(False, False) & ")" Next myA End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sverre" wrote: Is it posible automaticly to fond a blank line an put in a formula to calculate the average values above up to the next blank row ? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a blank line and put in a formula
Something is wrong in the frase:
cell.Offset(0,1).FormulaR1C1 ="=Average(R[" & -RowCount&"]C[-1]:R[-1]C[-1])" Cant figuer out what is wrong. I also want to calculate the average from column H to column AB Sverre skrev: Thank you, this was perfect, but if i want to start the calculation from column. G ? ryguy7272 skrev: This will calculate multiple averages, at each blank. If you just have one, that will work too: Sub PutInAverage() Dim myA As Range For Each myA In Selection.SpecialCells(xlCellTypeConstants, 23).Areas myA.Cells(myA.Rows.count + 1, 1).Resize(1, myA.Columns.count).Formula = _ "=Average(" & myA.Columns(1).Address(False, False) & ")" Next myA End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Sverre" wrote: Is it posible automaticly to fond a blank line an put in a formula to calculate the average values above up to the next blank row ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
v look up formula to generate a blank line | Excel Discussion (Misc queries) | |||
Moving formula results to blank line | Excel Worksheet Functions | |||
Find last blank row and use in a formula | Excel Programming | |||
Find Blank Line | Excel Programming | |||
Have a formula yield a true blank that disconnects graph line | Charts and Charting in Excel |