PDA

View Full Version : Solved: Use of Daverage in VBA



Bonaparte
11-15-2007, 06:16 AM
I have a excel worksheet with a structurethat looks something like this:

A B C D
1 Tree 4.4 Singapore
2 Tree 4.2 Singapore
3 Dog 5.2 Singapore


As I want the averageof category A to be listed for each row, I did something like this:

For x=0 to End_o_fRow

Sheet1.Cell(x,4).value=Daverage(database, field, criteria)

Next x

But somehow, i always receive a error msg #1004.
I suspect that I may have keyed in the parameters incorrectly:(
Any advice would be deeply appreciated:help

malik641
11-15-2007, 06:42 AM
Sheet1.Cell(x,4).value=Daverage(database, field, criteria)
Hey Bonaparte, Welcome to VBAX

It's not Cell(), but Cells(rowIndex, columnIndex). Also, x has to start out as 1, not zero (since there is no Zero index for a row or column).

Also, VBA doesn't know what you are talking about when you say "DAverage", you need to qualify it by putting "Worksheetfunction." in front of it.

So try changing your code to:

For x = 1 To End_of_Row
Sheet1.Cells(x, 5).Value = Worksheetfunction.DAverage(database, field, criteria)
Next x
Hope this helps

Bonaparte
11-15-2007, 06:52 AM
Hi Malik, thanks for the fast response.
tried your method, but it still generate the error.
I tried:
For x = 2 To 6
Set myRange = Worksheets("Sheet1").Range("A1:B6")
Worksheets("Sheet1").Cells(x, 3).Value = Application.WorksheetFunction.DAverage(myRange, 1, "=888")
Next x

Still get runtime error 1004:(

rory
11-15-2007, 06:55 AM
I suspect your criteria argument needs to be an actual range on the worksheet.

Bob Phillips
11-15-2007, 07:04 AM
If those are named ranges, you might get away with



Sheet1.Cells(x, 4).Value = Sheet1.Evaluate("DAverage(database, field, criteria)")

malik641
11-15-2007, 07:11 AM
For x = 2 To 6
Set myRange = Worksheets("Sheet1").Range("A1:B6")
Worksheets("Sheet1").Cells(x, 3).Value = Application.WorksheetFunction.DAverage(myRange, 1, "=888")
Next x

This code doesn't match the data you gave us in the first post. Could you provide a small workbook with some sample data?

Bonaparte
11-15-2007, 07:28 AM
I've attached a simpler version of the workbook.
It'll be great if i can use the D-functions (DMAX, DMIN, DAVERAGE).

I did dmax, and dmin using algorithim (not using worksheetfunction).
But as the data I'm dealing with can stretch up to 50,000 rows, the processing crawls...and take a very long time to process.:banghead:

Dat's y i'm thinking of using the D-functions to help me to speed up the processing. (and also becuase its so much simpler:hi:)

all suggestions are welcomed

malik641
11-15-2007, 07:58 AM
I don't think this is the fastest, but it works:

Public Sub UseDAverage()
Dim x As Long, lastRow As Long
Dim myRange As Excel.Range
Dim criteriaRange As Excel.Range
Dim temp(1) As Variant

lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Sheet1.Range("A1:B" & lastRow)
Set criteriaRange = Range("D1:D2")
temp(0) = myRange(1, 1).Value

For x = 2 To lastRow
temp(1) = "=""=" & Cells(x, 1).Value & """"
criteriaRange.Value = WorksheetFunction.Transpose(temp)

Cells(x, "C").Value = WorksheetFunction.DAverage(myRange, myRange(1, 2), criteriaRange)
Next
End Sub

File attached.

Bonaparte
11-15-2007, 08:12 AM
Thanks!
I spend 3 days on wad could have been a 1 hr job.:banghead:

Thanks for help!:beerchug:

malik641
11-15-2007, 08:18 AM
Glad to help :)

By the way, use this code instead. It will be faster (with 50,000 rows, every little bit counts):

EDIT: Forgot something.
Public Sub UseDAverage()
Application.ScreenUpdating = False
Dim x As Long, lastRow As Long
Dim myRange As Excel.Range
Dim criteriaRange As Excel.Range

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range("A1:B" & lastRow)
Set criteriaRange = Range("D1:D2")
criteriaRange(1, 1).Value = myRange(1, 1).Value

For x = 2 To lastRow
criteriaRange(2, 1).Value = "=""=" & Cells(x, 1).Value & """"
Cells(x, "C").Value = WorksheetFunction.DAverage(myRange, myRange(1, 2), criteriaRange)
Next
Application.ScreenUpdating = True
End Sub

Bonaparte
11-15-2007, 08:53 AM
Thanks for the help!:beerchug:
Took me some time to understand and modify the codes.
I can try out the codes at work tml!

You're the man:bow:

malik641
11-15-2007, 09:21 AM
No problem.

If/When you feel your question has been answered, be sure to mark this thread solved by clicking "Thread Tools" and selecting "Mark Thread Solved" then click "Perform Action".