• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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