loop through excel raw data file and input them to other excel formula sheet and perform iteration calculation

Question

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?

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