Average and report to another workbook

[Yiannis1234]

Hello everyone,

I am new to VBA and i would appreciate any help as i am pretty confused.

I have imported some csv from a folder to different tabs.
i need to calculate the average of the range A3:A7, then B3:B7 till E3:E7 and report their values on cells K7 to O7 and then do the same starting 4 cells lower i.e. A11 and report to K11 to O11 and loop for the entire workbook.

This is what i've done (found) so far.

Thank you

Sub csvtotabs()

Dim strPath As String
Dim strFile As String
 '
strPath = "C:\Users\jlymperopoulos\Desktop\gia macro\ccc\"
strFile = Dir(strPath & "*.csv")
Do While strFile <> ""
    With ActiveWorkbook.Worksheets.Add
        With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
            Destination:=.Range("A1"))
            .Parent.Name = Replace(strFile, ".csv", "")
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End With
    strFile = Dir
Loop

Set shtJT = ActiveWorkbook.ActiveSheet
Range("K7").Formula = "=Average(D4:D7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("L7").Formula = "=Average(E4:E7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("M7").Formula = "=Average(F4:F7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("N7").Formula = "=Average(G4:G7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("O7").Formula = "=Average(H4:H7)"

End Sub

[ddanbe]

Hmm, you seem to use other range in your question then in your code.
Here is how you can loop through a range of cells.

[Yiannis1234]

Hi and thanks for answering,

sorry but i don't get it.

What I need to do is get the average of the first 4 values in the D row, then the first 4 values of E row etc till row H and report their average values to blank cells. But i need to loop this action (for the next four values etc) for all workbook.
I have made only the averaging part at the end of my code, but i cannot loop this to get the next four values and so on...

Set shtJT = ActiveWorkbook.ActiveSheet
Range("K7").Formula = "=Average(D4:D7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("L7").Formula = "=Average(E4:E7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("M7").Formula = "=Average(F4:F7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("N7").Formula = "=Average(G4:G7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("O7").Formula = "=Average(H4:H7)"

[ddanbe]

Did the examples of looping where not clear enough?

[ddanbe]

Did the examples of looping I gave you, were not clear enough?

[ddanbe]

Did the examples of looping I gave you, were not clear enough?

[Yiannis1234]

Well, i am having a hard time since i started VBA last Friday. i have made some progress i guess by selecting the next +4 cell :). It's slow i know. i am working on it though!

But i think that i need to make my averaging more general to apply to all cells that i am interested about

Sub csvtotabs()

Dim strPath As String
Dim strFile As String
 '
strPath = "C:\Users\jlymperopoulos\Desktop\gia macro\ccc\"
strFile = Dir(strPath & "*.csv")
Do While strFile <> ""
    With ActiveWorkbook.Worksheets.Add
        With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
            Destination:=.Range("A1"))
            .Parent.Name = Replace(strFile, ".csv", "")
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End With
    strFile = Dir
Loop

Dim x As Integer

  NumRows = Range("D4", Range("D4").End(xlDown)).Rows.Count

  Range("D4").Select

  For x = 4 To NumRows

Set shtJT = ActiveWorkbook.ActiveSheet
Range("K7").Formula = "=Average(D4:D7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("L7").Formula = "=Average(E4:E7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("M7").Formula = "=Average(F4:F7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("N7").Formula = "=Average(G4:G7)"
Set shtJT = ActiveWorkbook.ActiveSheet
Range("O7").Formula = "=Average(H4:H7)"

     ActiveCell.Offset(4, 0).Select
  Next

End Sub

[ddanbe]

I was more thinking in this direction:

Set sum = 0.0
For Counter = 1 To 4 'sum cells A1:A4
        Set curCell = Worksheets("Sheet1").Cells(Counter, 1)
        sum = sum + curCell
 Next Counter
Set average = sum / 4 etc.

Have to go, perhaps see you back tomorrow. Bye.

[Yiannis1234]

Hello everyone,

as i explained before, i started VBA last Friday. Well, this was the first day i actually figured some code for myself and to tell you the truth i am pretty happy!!
I am attaching my (monsterous) code below in case anyone would care to help me improve it. In general it works for me.

What it does is importing some csv from a folder and than takes group of 4 values of columns D to H and reports their mean (hourly) values at the end af each sheet.

I have attached two of the input files in case you are interested to see how it works,

Thank you in advance
Cheers!

Sub csvtotabs()

Dim strPath As String
Dim strFile As String

strPath = "C:\Users\jlymperopoulos\Desktop\gia macro\ccc\"
strFile = Dir(strPath & "*.csv")
Do While strFile <> ""
       With Worksheets.Add
        With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
        Destination:=.Range("A1"))
            .Parent.Name = Replace(strFile, ".csv", "")
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False

        End With  
    End With  
    strFile = Dir    
Loop

Dim w As Worksheet
For Each w In ThisWorkbook.Worksheets
ActiveSheet.Range("D7").Activate
Do Until IsEmpty(ActiveCell.Value)
ActiveSheet.Range("D7").Activate
Range("AN7").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-3]C[-36]:RC[-36])"
Range("AN8").Select
Range("AN7").Select
Selection.AutoFill Destination:=Range("AN7:AR7"), Type:=xlFillDefault
Range("AN7:AR7").Select
Range("AN7:AR10").Select
Selection.AutoFill Destination:=Range("AN7:AR99"), Type:=xlFillDefault
Range("AN7:AR99").Select
ActiveWindow.SmallScroll Down:=-81
Range("D2:H2").Select
Selection.Copy
Range("AN6").Select
ActiveSheet.Paste
Columns("AN:AR").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A7:A30").Select
Selection.Copy
Range("AL2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=9
ActiveWindow.SmallScroll Down:=-6
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-9
Range("AM2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AM2:AM25"), Type:=xlFillDefault
Range("AM2:AM25").Select

Worksheets(ActiveSheet.Index + 1).Select

Loop
Next w
Worksheets(1).Activate

End Sub