I have two excel raw data tables called meandiretcion (image1) and meanspeed (image2). I also have another excel file with formula (image3), and the excel formula file has 3 sheet (sheet1, sheet2, sheet3).

The aim is to perform iterative calculation on excel formula file using the column values from two excel tables with raw data.

For example, column1 of meandirection will be input to sheet1.columnF, column1 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved.

And then column2 of meandirection will be input to sheet1.columnF, column2 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved.

And then column3 of meandirection will be input to sheet1.columnF, column3 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved...

How do I perform this automatic task?

I have two excel raw data tables called meandiretcion (image1) and meanspeed (image2). I also have another excel file with formula (image3), and the excel formula file has 3 sheet (sheet1, sheet2, sheet3).

The aim is to perform iterative calculation on excel formula file using the column values from two excel tables with raw data.

For example, column1 of meandirection will be input to sheet1.columnF, column1 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved.

And then column2 of meandirection will be input to sheet1.columnF, column2 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved.

And then column3 of meandirection will be input to sheet1.columnF, column3 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved...

How do I perform this automatic task?

• excel
• vba
• loops

• 1 Try recording a macro while performing a couple of iterations manually - that should give you a starting point. You're more likely to get help here if you tell us what you already tried and what specific problems you ran into while doing that. i.e. what exactly is preventing you from getting this set up? If it's because you don't know any VBA then this is not a good place to start learning: here is where you'd come to get help after you've started writing some code. – Tim Williams Commented Jan 31 at 16:05
• Either copy/paste or change column references in cell formulas, either can be done with code. – June7 Commented Jan 31 at 18:43

1 Answer 1

Option Explicit

Sub ProcessRawData()

    Dim wb As Workbook, wsDir As Worksheet, wsSpeed As Worksheet
    Dim wbCalc As Workbook, wbResult As Workbook
    Dim lastRow As Long, lastCol As Long, r As Long, c As Long
    Dim arDir, arSpeed, arResult, sPath As String, sName As String
    
    Set wb = ThisWorkbook
    Set wsDir = wb.Sheets(1) ' meandirection
    Set wsSpeed = wb.Sheets(2) 'meanspeed
    
    sPath = ThisWorkbook.Path
    Set wbCalc = Workbooks.Open(sPath & "/formulas.xlsm") ' change
    
    ' new workbook for results
    Set wbResult = Workbooks.Add()
   
    Application.ScreenUpdating = False
    With wsDir
    
        ' size of data
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        ' scan days
        r = lastRow - 1
        For c = 1 To lastCol
           
           ' read rawdata into arrays
           arDir = wsDir.Cells(2, c).Resize(r)
           arSpeed = wsSpeed.Cells(2, c).Resize(r)
           
           'copy into calc
           wbCalc.Sheets(1).Range("F2:F" & lastRow) = arDir
           wbCalc.Sheets(2).Range("M2:M" & lastRow) = arSpeed
          
           ' save results
           arResult = wbCalc.Sheets(3).Range("P2:P" & lastRow)
           wbResult.Sheets(1).Cells(1, c) = wsDir.Cells(1, c) ' day
           wbResult.Sheets(1).Cells(2, c).Resize(r) = arResult
           
        Next
    End With
    Application.ScreenUpdating = False

    ' save result workbook
    sName = Format(Now(), "yyyymmdd_MMSS") & "_Results.xlsx"
    wbResult.Close savechanges:=True, Filename:=sName
    MsgBox lastCol & " columns processed", vbInformation

End Sub