• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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
0
sharkyboy
Asked:
sharkyboy
  • 4
  • 3
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
I find this formula much more convenient than a VBA

=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)),""))

paste this formula in B2 and copy it down and across

See attached file

Saqib
Copy-of-Xl0000018.xls
0
 
sharkyboyAuthor Commented:
great but i forgot to told that the number of row can change but the last row will always be #div/0!
0
 
Saqib Husain, SyedEngineerCommented:
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,"")))
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
chwong67Commented:
Try Macro Below...
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

Open in new window

0
 
sharkyboyAuthor Commented:
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 ?
0
 
chwong67Commented:
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

Open in new window

0
 
sharkyboyAuthor Commented:
Can I only change my b1 cell the get the number that i need ? Thank you so much
0
 
chwong67Commented:
Amended Copy
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
    If ActiveSheet.Cells(1, i).Value = "" Then Exit For
    max_column = i
Next

For r = 2 To lastrow
    For i = 2 To max_column
        mcolumn = ActiveSheet.Cells(1, i).Value
        If mcolumn = "" Or mcolumn > lastrow - r + 1 Then
        ElseIf mcolumn = lastrow - r + 1 Then
           ActiveSheet.Cells(r, i).Formula = "=SUM(A" & r & ":A" & lastrow & ")/" & mcolumn
           ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
           'Exit For
        Else
           ActiveSheet.Cells(r, i).Formula = "=(R[0]C[" & i * -1 + 1 & "]*(2/(" & mcolumn & "+1))+R[1]C[" & i - i & "]*(1-(2/(" & mcolumn & "+1))))"
           ActiveSheet.Cells(r, i).NumberFormat = "#,##0.00"
        End If
    Next i
Next r
End Sub

Open in new window

0
 
sharkyboyAuthor Commented:
Great thank you very much, how can I execute my macro my changing my b1 cell ? thank you, I start will vba :S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now