Reading in average of cells inside a FOR loop

[ch46guy]

I'm working within a nested FOR loop. If you notice when I'm Assigning "ECD" I'm trying to average the cells from J (which is row 1) and column 11 to column 12. It keeps on telling me "Application-Defined or object-defined error". Any help would be appreciated.

Thanks,

Sub copytab()
Dim WSName As String
Dim NumbSheets As Integer
Dim NextRow As Integer
Dim i As Integer


Application.DisplayAlerts = False
If (Worksheets("MIPDATA").Name <> "") Then ThisWorkbook.Worksheets("MIPDATA").Delete
Application.DisplayAlerts = True
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MIPDATA"
NumbSheets = Worksheets.Count
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 1) = "X"
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 2) = "Y"
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 4) = "@@EC"
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 5) = "ECD"
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 6) = "PID"
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 7) = "FID"
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 8) = "XSD"
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 9) = "MIP_Boring"
ThisWorkbook.Worksheets("MIPDATA").Cells(1, 10) = "TOP"


ThisWorkbook.Worksheets("MIPDATA").Cells(2, 1) = "Depth"
ThisWorkbook.Worksheets("MIPDATA").Cells(2, 2) = "Feet"


ThisWorkbook.Worksheets("MIPDATA").Cells(3, 1) = "=Count(A1:A100000)"
ThisWorkbook.Worksheets("MIPDATA").Cells(3, 2) = "5"
ThisWorkbook.Worksheets("MIPDATA").Cells(3, 4) = "mS/m"
ThisWorkbook.Worksheets("MIPDATA").Cells(3, 5) = "µV"
ThisWorkbook.Worksheets("MIPDATA").Cells(3, 6) = "µV"
ThisWorkbook.Worksheets("MIPDATA").Cells(3, 7) = "µV"
ThisWorkbook.Worksheets("MIPDATA").Cells(3, 8) = "µV"


NextRow = ThisWorkbook.Worksheets("MIPData").Cells(65000, 1).End(xlUp).Row + 1


NumbSheets = NumbSheets - 1


For i = 1 To NumbSheets


WSName = ThisWorkbook.Worksheets(i).Name




Dim RowNum As Long
Dim Depth As Long
Dim EC As Long
Dim ECD As Long
Dim PID As Long
Dim FID As Long
Dim XSD As Long
RowNum = ThisWorkbook.Worksheets(WSName).Cells(Rows.Count, "a").End(xlUp).Row


    For j = 1 To RowNum
    EC = ThisWorkbook.Worksheets(WSName).Cells(j, 1)
   
    ECD = Application.WorksheetFunction.Average(Worksheets(WSName).Range(Cells(j, 11)), Cells(j, 12))
    
    
    j = j + 5
    Next j


RowNum = 0
Next i

 

[jsotola]

change this line

RowNum = .Cells(Rows.Count, "a").End(xlUp).Row ' finds last non blank in column 1 aka column "A"


to


RowNum = .Cells(Rows.Count, 11).End(xlUp).Row ' finds last non blank in column 11