Average recordeset field or worksheet column

[felipedmc]

I have a database I use to fill a recordset used to fill several sheets in excel.
How do I average an only numbers field of the recordset or the column it fills in excel worksheet?
the registers number is not fixed. So the Range() method can't have arguments like "B1:B5". It's not previously defined, depends on the query return.The number of registers can change as i query the data from lots of tables (but I work with one table each step of a loop).

 

[strive4peace]

rather than calculating the values, I prefer to write formulas to Excel.

   xlSS.range("X" & nLastRow +2).Formula = "=Average("X2:X:" & nLastRow & ")"

WHERE
xlSS is the object variable for the spreadsheet
X is the column letter
nLastRow is calculated using something such as --> xlSS.cells(xlSS.rows.count,1).end(xlup).row

 

[felipedmc]

Thanks strive! I have some doubts:

1) I can't put the result into a cell in the same spreadsheet that my values are in. I was tryin' to put the Average() result into a variable that will fill another cell in another workbook. To use the .Formula property i think i'd have to link both workbooks. Am I right?

2) The methods you are using to define nLastRow. What do they return exactly? I tried defining my number of rows using the xlSS.rows.count property, but it returned 10401 in a sheet with only 47 register rows. Very strange...

3) What does this end() method does exactly?

4) Why you are adding 2 to the nLastRow here "xlSS.range("X" & nLastRow +2)" ?

Thank you so much! I'm a month stuck in this *#%+ ! I gotta finish this in Jan

 

[felipedmc]

the cLastRow "trick" worked just fine. But I'm geting the runtime error: '1004' Unable to get Average property of the Worksheetfunction class". I searched a little about it here in the forum, but didn't find anything conclusive. I am using Acess to create and manipulate excel workbooks and worksheets via VBA code. So it's not just Excel that is envolved.

And now the wierdest #$%@ing !@#$ ever. As I was writing this reply i tried my code once again and it worked! But then i tried again and the same error came back to haunt my soul! Anybody knows what is happening?

Here's some code

      objRng.CopyFromRecordset rcdSet
                objWks.Columns.AutoFit
                lastRow = objWks.Cells(Rows.Count, "B").End(xlUp).Row
                objWks.Range("B" & lastRow + 2).Formula = objXL.WorksheetFunction.Average(Range("B1", "B" & lastRow))
                Debug.Print "MEDIA DE " & MonthName(codMon) & " = " & objWks.Range("B" & lastRow + 2).Value

I desperatly need this, so PLEASE HEEEEEEELP MEEEE SOS SOS SOS !!!!!