Copying averages from multiple csv files to a summary xls using VBA

Hi all,

As a cell biology researcher I am not too familiar with VBA and programming in general, so please bear in mind I'm a beginner. After spending quite some time trying to find an online solution to my problem on various message boards, I decided to join up and share my challenge with you guys.

The problem is as follows: I have a cell stimulation setup that produces a small csv file every ~150 seconds (while experiments with this setup can last for days...). This file contains 6 columns, 4 of which describe measured values in different channels. From each file I'd like to extract the average signal (rows 30-80) minus the average background (rows 90-109) for each of the four channels (columns C-F). Signal minus background *1000/0.5 gives me the electrical resistance of my stimulated cells.

Making use of different bits of code found online and typed by yours truly, I compiled the following macro:

Sub Data_extractor()

Application.ScreenUpdating = False

Dim F As String
Dim roww As Long
roww = 0
Dim FileLocSpec As String
FileLocSpec = "D:\Data\*.csv"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop

Set r = Range("A1")
Dim rowx As Long
rowx = 1
While r.Value <> ""
Workbooks.Open Filename:="D:\Data\" & r.Value
    
    Range("C3").FormulaR1C1 = "=AVERAGE(R[7]C:R[78]C)"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[86]C:R[105]C)"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C-R[-1]C"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/0.5*1000"
    Range("C6").Select
    ActiveWindow.SmallScroll Down:=-8
    Range("C3:C6").Select
    Selection.AutoFill Destination:=Range("C3:F6"), Type:=xlFillDefault
    Range("C6:F6").Select
    Selection.Copy

Range(Cells(rowx, 2), Cells(rowx, 5)).Select
Range(r, 2).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Set r = r.Offset(1, 0)
rowx = rowx + 1
Wend

Application.ScreenUpdating = True

End Sub

What I want it to do is list the filenames of the csv files in column A, and resistances for channels 1-4 in columns B-E next to the name of the file they're derived from. The code as given above, however, only results in a 'runtime error 1004: application-defined or object-defined error'. All suggestions and improvements are welcome, since manual processing of these files is not exactly an option

Thanks in advance for your help!

Dirk-Jan

PS: Since I wasn't allowed to upload csv files I converted a sample file to xls before uploading it to give you an idea of the data involved.