sharkyboy
asked on
Excel vba question
See the attachement
when I put the number 2 in my b1 cell,my B11 cell calculated the average of a11 and a12 then I used
the average to calculate the next cell. In my column 3, the c10 calculates the average of a12,a11,a10
Then I used the last periods of the cell c10 to calculate the other cells in column c.
My question is how could I do with VBA to put the number 4 in d1 then to calculate the average of a12,a11,a10,a9 in my d9 cell
expert-exchange.xlsx
when I put the number 2 in my b1 cell,my B11 cell calculated the average of a11 and a12 then I used
the average to calculate the next cell. In my column 3, the c10 calculates the average of a12,a11,a10
Then I used the last periods of the cell c10 to calculate the other cells in column c.
My question is how could I do with VBA to put the number 4 in d1 then to calculate the average of a12,a11,a10,a9 in my d9 cell
expert-exchange.xlsx
ASKER
great but i forgot to told that the number of row can change but the last row will always be #div/0!
I am not sure that I fully understand you but if I do you can try this formula
=IF(ROW()+B$1<13,(A2*(2/(B $1+1))+B3* (1-(2/(B$1 +1)))),IF( ROW()+B$1< 14,AVERAGE (OFFSET($A $1,12-B$1, 0,B$1)),IF (ROW()+B$1 <15,1/0,"" )))
=IF(ROW()+B$1<13,(A2*(2/(B
Try Macro Below...
Assumption: Your existing formula is correct..
Assumption: Your existing formula is correct..
Sub Fill_formula()
lastrow = ActiveSheet.Columns(1).Find(What:="#DIV/0!", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row - 1
For i = 2 To lastrow - 1
ActiveSheet.Columns(i).EntireColumn.Delete
ActiveSheet.Cells(1, i) = i
ActiveSheet.Cells(1, i).NumberFormat = "###0"
Next i
'Set Rng = ActiveSheet.Range("A1:A" & lastrow)
For r = 2 To lastrow
For i = 2 To lastrow - r + 1
If i = lastrow - r + 1 Then
ActiveSheet.Cells(r, i).Formula = "=SUM(A" & r & ":A" & lastrow & ")/" & i
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
Exit For
Else
ActiveSheet.Cells(r, i).Formula = "=(R[0]C[" & i * -1 + 1 & "]*(2/(" & i & "+1))+R[1]C[" & i - i & "]*(1-(2/(" & i & "+1))))"
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
End If
Next i
Next r
End Sub
ASKER
chwong the macro works well but can i have only one column and only change the number to have the column. For exemple, I only need the column B, can I change the number 2 by 4 then after changing the number get the 4 column ?
Amended as below
Sub Fill_formula()
lastrow = ActiveSheet.Columns(1).Find(What:="#DIV/0!", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row - 1
max_column = 4 '<--- Change the column here
For i = 2 To max_column
ActiveSheet.Columns(i).EntireColumn.Delete
ActiveSheet.Cells(1, i) = i
ActiveSheet.Cells(1, i).NumberFormat = "###0"
Next i
For r = 2 To lastrow
For i = 2 To lastrow - r + 1
If i > max_column Then Exit For
If i = lastrow - r + 1 Then
ActiveSheet.Cells(r, i).Formula = "=SUM(A" & r & ":A" & lastrow & ")/" & i
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
Exit For
Else
ActiveSheet.Cells(r, i).Formula = "=(R[0]C[" & i * -1 + 1 & "]*(2/(" & i & "+1))+R[1]C[" & i - i & "]*(1-(2/(" & i & "+1))))"
ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
End If
Next i
Next r
End Sub
ASKER
Can I only change my b1 cell the get the number that i need ? Thank you so much
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great thank you very much, how can I execute my macro my changing my b1 cell ? thank you, I start will vba :S
=IF(ROW()+B$1<13,(A2*(2/(B
paste this formula in B2 and copy it down and across
See attached file
Saqib
Copy-of-Xl0000018.xls