building a table from another spreadsheet? (Excel 2007 VBA)

Question

I try to build a table using VBA. The source data is in 1 spreadsheet and the result in another sheet of the same file.

Example:

ColumnA(Sheet1)  - ColumnA(Sheet2)

Title A            Title B                            

12.1                                     

43.8               Average(A2:A3)                            

8.7                 Average(A3:A4)  

6.5                Average(A4:A5) 

7.4                Average(A5:A6) 

I work in VBA because this is the first step of a larger table.

Thank you.

Answer 1

It is not clear what is the concept for building formulas.Apparently you want a moving average.But what is the use of Input 2.

Can you upload a file in skydrive/sendspace and share us the link.

Clearly show how Sheet3  will look after macro runs and how you are deriving the formulas.

We can definitely help you better then.

Answer 2

I am little confused by the fact that RowA, RowB, ..., seem to refer to columns. Am I right about this?

Can you also be more specific about why you want to use VBA in the first place? In other words, what do you want to do that cannot be done with standard formulas and core Excel functionalities?

---

The Data Specialist (Blog)

Answer 3

Thank you for your comments. I have simplified the example above.

I have run the macro recorder, and received the following below.

Can this be written in a better and more generic way, taking into account the example I provide above?

[code]

Option Explicit

Sub ATestrecorder()
'
' ATestrecorder Macro
' record macro

    ActiveCell.FormulaR1C1 = "=RC[-8]"
    Range("I1").Select
    Selection.AutoFill Destination:=Range("I1:N1"), Type:=xlFillDefault
    Range("I1:N1").Select
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(Datatest!RC[-8]:R[1]C[-8])"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K11"), Type:=xlFillDefault
    Range("K2:K11").Select
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(Datatest!R[-1]C[-9]:R[1]C[-9])"
    Range("L3").Select
    Selection.AutoFill Destination:=Range("L3:L11"), Type:=xlFillDefault
    Range("L3:L11").Select
End Sub

[/code]

Answer 4

A first step would be to clean the code, so as to remove references to current selection.

In your example, this would look like this

     Range("I1").FormulaR1C1 = "=RC[-8]"
     Range("I1").AutoFill Destination:=Range("I1:N1"), Type:=xlFillDefault
   
     Range("K2").FormulaR1C1 = "=AVERAGE(Datatest!RC[-8]:R[1]C[-8])"
     Range("K2").AutoFill Destination:=Range("K2:K11"), Type:=xlFillDefault
   
     Range("L3").FormulaR1C1 = "=AVERAGE(Datatest!R[-1]C[-9]:R[1]C[-9])"
     Range("L3").AutoFill Destination:=Range("L3:L11"), Type:=xlFillDefault

Regarding a more generic way, this depends on what you want to achieve.

My personal preferences would lead me to keep formulas entry in the destination worksheet itself, and just use VBA for the fill down operation. Something like that:

Public Sub FillRange(dataRange As Range, formulaRange As Range)
    Dim destinationRange As Range
    Set destinationRange = Range(formulaRange.Cells(1, 1), formulaRange.End(xlToRight).Offset(dataRange.Rows.Count - 1))
    formulaRange.Rows(1).AutoFill Destination:=destinationRange, Type:=xlFillDefault
End Sub

With the function being called like that, for example:
 FillRange [A2:A5], [C2:E2]

---

The Data Specialist (Blog)

Answer 5

Thank you. I have a few follow-up questions.

Is there a way that I can write in range(I1:N1) a series of titles, e.g. range of "title1", "title2", "title3", etc for those cells?

And can I calculate the average with regards to a specific column (e.g. column D) instead? So that for example in 'column I'  the average of every 2 cells in column D is calculated, in 'columnJ' the average of column E?

Answer 6

Yes, you can assign content to a cell with VBA, using the Value or Value2 properties of the range object.

Regarding, the rolling average: this is what your formulas do already, or did I miss something?

---

The Data Specialist (Blog)