Reading in average of cells inside a FOR loop

C

ch46guy

New Member
Joined
Apr 29, 2013
Messages
29
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,

Code: 
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Code: 
    ' this one is yours
[B]    ECD = Application.WorksheetFunction.Average(Worksheets(WSName).Range(Cells(j, 11)), Cells(j, 12))
[/B]
    ' i think that you wanted this
[B]    ECD = Application.WorksheetFunction.Average(Worksheets(WSName).Range(Cells(j, 11), Cells(j, 12)))[/B]

' best way to troubleshoot this kind of a program bug is to do this command

[B]Worksheets(WSName).Range(Cells(j, 11)), Cells(j, 12).select[/B]

and check if the correct area is selected

there was a couple of logic errors in the code

the if command checking whether worksheet named "MIPDATA" has no name, that check will always fail
so just delete the sheet Sheets("MIPDATA") 

the code does not exclude Sheet("MIPDATA") from calculating avearges
it excludes the last sheet ...  that could get you into trouble

(the code you posted does not have "End Sub", so i assume that there is more to it)

when referring to worksheets, you do not need to get the name of Sheets(1) and then refer to sheet by its name
just refer to Sheets(1)


Code: 
Sub copytab()
    Dim WSName As String
    Dim NextRow As Integer
    Dim i As Integer
    
    
    Application.DisplayAlerts = False
    
    ThisWorkbook.Sheets("MIPDATA").Delete
    
    Application.DisplayAlerts = True
    
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "MIPDATA"
    With ThisWorkbook.Sheets("MIPDATA")
        
        .Cells(1, 1) = "X"
        .Cells(1, 2) = "Y"
        .Cells(1, 4) = "@@EC"
        .Cells(1, 5) = "ECD"
        .Cells(1, 6) = "PID"
        .Cells(1, 7) = "FID"
        .Cells(1, 8) = "XSD"
        .Cells(1, 9) = "MIP_Boring"
        .Cells(1, 10) = "TOP"
        
        .Cells(2, 1) = "Depth"
        .Cells(2, 2) = "Feet"
        
        .Cells(3, 1) = "=Count(A1:A100000)"
        .Cells(3, 2) = "5"
        .Cells(3, 4) = "mS/m"
        .Cells(3, 5) = "µV"
        .Cells(3, 6) = "µV"
        .Cells(3, 7) = "µV"
        .Cells(3, 8) = "µV"
    End With
    
    NextRow = 4   ' not used
    
    For i = 1 To ThisWorkbook.Sheets.Count - 1
    
        Dim RowNum As Long
        Dim Depth As Long
        Dim EC As Long
        Dim ECD As Double
        Dim PID As Long
        Dim FID As Long
        Dim XSD As Long
    
        RowNum = ThisWorkbook.Sheets(i).Cells(Rows.Count, "a").End(xlUp).Row
    
        For j = 1 To RowNum Step 5
            ThisWorkbook.Sheets(i).Cells(j, 1).Select
            Range(Cells(j, 11), Cells(j, 12)).Select
            EC = ThisWorkbook.Sheets(i).Cells(j, 1)
            ECD = Application.WorksheetFunction.Average(ThisWorkbook.Sheets(i).Range(ThisWorkbook.Sheets(i).Cells(j, 11)), ThisWorkbook.Sheets(i).Cells(j, 12))
        Next j
        
    Next i
End Sub
 
Upvote 0
Thank you very much for all your help. Still getting the same bug, but I'll keep on working on it.
 
Upvote 0
try changing it to:
ECD = Application.WorksheetFunction.Average(Worksheets(WSName).Range("K" & j), ("L" & j)))

and see if that is any better.
 
Upvote 0
i screwed it up, the bracket is in the wrong place in the line that gives an error

here is the corrected version


i added a few "select" lines

single step through the code using F8 key, watch the worksheets and confirm that the correct ranges are being selected

either comment out those lines or delete them when code works correctly




Code: 
Sub copytab()
    Dim WSName As String
    Dim NextRow As Integer
    Dim i As Integer
    
    
    Application.DisplayAlerts = False
    
    ThisWorkbook.Sheets("MIPDATA").Delete
    
    Application.DisplayAlerts = True
    
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "MIPDATA"
    With ThisWorkbook.Sheets("MIPDATA")
        
        .Cells(1, 1) = "X"
        .Cells(1, 2) = "Y"
        .Cells(1, 4) = "@@EC"
        .Cells(1, 5) = "ECD"
        .Cells(1, 6) = "PID"
        .Cells(1, 7) = "FID"
        .Cells(1, 8) = "XSD"
        .Cells(1, 9) = "MIP_Boring"
        .Cells(1, 10) = "TOP"
        
        .Cells(2, 1) = "Depth"
        .Cells(2, 2) = "Feet"
        
        .Cells(3, 1) = "=Count(A1:A100000)"
        .Cells(3, 2) = "5"
        .Cells(3, 4) = "mS/m"
        .Cells(3, 5) = "µV"
        .Cells(3, 6) = "µV"
        .Cells(3, 7) = "µV"
        .Cells(3, 8) = "µV"
    End With
    
    NextRow = 4   ' not used
    
    For i = 1 To ThisWorkbook.Sheets.Count - 1
    
        Dim RowNum As Long
        Dim Depth As Long
        Dim EC As Long
        Dim ECD As Double
        Dim PID As Long
        Dim FID As Long
        Dim XSD As Long
        
        With ThisWorkbook.Sheets(i)
        
            .Select        ' debug
            
            RowNum = .Cells(Rows.Count, "a").End(xlUp).Row
            
            For j = 1 To RowNum Step 5
                .Cells(j, 1).Select                                    ' debug
                .Range(.Cells(j, 11), .Cells(j, 12)).Select            ' debug
    
                EC = .Cells(j, 1)
                ECD = Application.WorksheetFunction.Average(Range(.Cells(j, 11), .Cells(j, 12)))
            Next j
            
        End With
        
    Next i
End Sub
 
Last edited:
Upvote 0
you are welcome

one more error though

this line should have a dot before "Rows" like this

RowNum = .Cells(.Rows.Count, "a").End(xlUp).Row

because that part of the command is actually

ThisWorkbook.Sheets(i).Rows.Count


it is shortened by the "with" command
 
Upvote 0
Thanks. One more question. Since some columns are longer than others, when I go through the assignment of ECD, if there is nothing there it gives me an error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
511
Alex Blakenburg
A
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
900
Alex Blakenburg
A
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
396
rahildhody
R
B
  • Question Question
Copy formula into a cell using vba
2
Replies
11
Views
832
BobtBuilder
B
Young Grasshopper
  • Solved
Excel VBA / Loop to copy unknown number of columns underneath each other, and string to each cell, AND adjecent cells!
Replies
4
Views
838
kevin9999
K

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top