first and last row and then average values

[caerdydd]

Hi all,
I have taken and amended Loomah's first and last row from the FAQ section.

Thus I have:

dim therange as range
Set therange = Worksheets("Data").Range(Cells(firstrow, firstcol), _
Cells(LastRow, firstcol)).Select

answer = Application.WorksheetFunction.Average(therange)

But keep getting an error message of 'Object required' on the line 'set therange' which I do not understand in this context.
Can anyone help
Best regards

 

[SkipVought]

No Select method with Set.

dim therange as range
Set therange = Worksheets("Data").Range(Cells(firstrow, firstcol), _
Cells(LastRow, firstcol))
    
answer = Application.Average(therange)

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[caerdydd]

Thanks for the reply Skip, but i tried this and still get object or application defined error.

 

[SkipVought]

what are the values for firstrow, firstcol, lastrow??

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[caerdydd]

Skip thanks again, here is the full procedure

Sub FindLastRow()
Dim therange As Range
Dim therange1 As Range
Dim myrange As Range
Dim w As Integer, i As Integer
Dim mycode As Range

DoEvents

firstrow = ThisWorkbook.Sheets(2).Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row

firstcol = ThisWorkbook.Sheets(2).Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Column
w = 1
i = 2

firstrow = 2

LastRow = ThisWorkbook.Sheets("Data").Cells.SpecialCells(xlCellTypeLastCell).Row
Set mycode = ThisWorkbook.Sheets("Main").Range("A" & i)

Do Until mycode.Value = Empty
Set mycode = ThisWorkbook.Sheets("Main").Range("A" & i)
If mycode.Value <> "" Then
DoEvents
Set therange = Worksheets("Data").Range(Cells(firstrow, firstcol), _
Cells(LastRow, firstcol))

answer = Application.WorksheetFunction.Average(therange)

ThisWorkbook.Sheets("setup").Range("todaystime").Value Then
ThisWorkbook.Sheets(1).Range("E1").Offset(w, 0).Value = answer

firstcol = firstcol + 1
w = w + 1
i = i + 1
End If
Loop

End Sub

the values for the first col, first row, and last row are what i am expecting. When select therange is used it grabs the appropriate data.

 

[SkipVought]

I did not ask for the code, (BTW use code /code rather than quote /quote) although I do appreciate you positing...

I was asking for the VALUES -- from the debugger. Add A Watch Window.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[caerdydd]

values are first row always set to 2, first col set to 2 and then adds one after every loop etc.

Where i can see the problem is that
'Set therange = thisworkbook.sheets("Data").Range(Cells(firstrow, firstcol), _
Cells(LastRow, firstcol))

will only work without the error if you ahppen to have the data sheet as the active sheet when the macro runs if you are in a different sheet then the error object required occurs.

 

[SkipVought]

you code has

            Set therange = Worksheets("Data").Range(Cells(firstrow, firstcol), _
            Cells(LastRow, firstcol))

ADD THE ThisWorkbook OBJECT.

            Set therange = ThisWorkbook.Worksheets("Data").Range(Cells(firstrow, firstcol), _
            Cells(LastRow, firstcol))

You should NOT need to have the DATA sheet active.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[PHV]

IMHO the problem is the unqualified Cells references.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

PHV,

You're ABSOLUTELY correct!!!

   with Worksheets("Data")
      Set therange = .Range(.Cells(firstrow, firstcol), _
            .Cells(LastRow, firstcol))
   end with

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}