
Nov 1st, 2010, 10:21 AM
#1
Thread Starter
New Member
[RESOLVED] [Excel] Workbook Function Error with Variables
Hi Everyone!
I am trying to create a final table of statistical data from a large database export. I have been successful at parsing the data into a new workbook by sheets, now I just need to run some basic Excel stats functions on one column in each sheet and have the results displayed in one table. However, I get the runtime Error 1004 at line 19 in my following code:
Code:
1. ' Populate table with analyte information
2. Dim iSheetCount As Integer
3. Dim iSheet As Integer
4. Dim totalrows As Integer
5. Dim dMedian As Integer
6. Dim dMode As Integer
7. Dim dQuartile1 As Integer
8. Dim dQuartile3 As Integer
9. Dim dGeoMean As Integer
10.
11. iSheetCount = ActiveWorkbook.Worksheets.Count
12. For iSheet = 2 To iSheetCount  2
13. Worksheets(iSheet).Activate
14.
15. For i = 3 To 7
16. ' Define Stats for table
17. totalrows = ActiveSheet.UsedRange.Rows.Count
18. dMedian = WorksheetFunction.Median(Columns(totalrows, 7))
19. dMode = WorksheetFunction.Mode(Columns(totalrows, 7))
20. dQuartile1 = WorksheetFunction.Quartile(Columns(totalrows, 7), 1)
21. dQuartile3 = WorksheetFunction.Quartile(Columns(totalrows, 7), 3)
22. dGeoMean = WorksheetFunction.GeoMean(Columns(totalrows, 7))
23. ' Populate table with stats
24. Sheets("Table").Cells(i, 3).Value = dMedian
25. Sheets("Table").Cells(i, 4).Value = dMode
26. Sheets("Table").Cells(i, 5).Value = dQuartile1
27. Sheets("Table").Cells(i, 6).Value = dQuartile3
28. Sheets("Table").Cells(i, 7).Value = dGeoMean
29.
30. Next i
31.
32. Next iSheet
Is it possible to work with variables in function commands? Did I make an error in my semantics? Any help with be GREATLY appreciated!!
Thank you so much!
Last edited by funkykizzy; Nov 1st, 2010 at 01:59 PM.
Reason: I forgot to add my question in at the bottom ;)

Nov 1st, 2010, 03:33 PM
#2
Re: [Excel] Workbook Function Error with Variables
it maybe that mode is one of the worksheet functions that does not work in VBA, only as a formula
there are a few of these
even
?Application.WorksheetFunction.Mode(2,3,2,5)
gives an error, but as a formula works fine
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete

Nov 2nd, 2010, 08:57 AM
#3
Thread Starter
New Member
Re: [Excel] Workbook Function Error with Variables
Thanks Westconn1!
I was afraid of that...I heard that this might happen but I was hoping that it would be something else.
The only other way I can think of accomplishing my task is to write out each computation for the final table per sheet.
Such as (from recording the action):
1. Sub Stats()
2. Range("C2").Select
3. ActiveCell.FormulaR1C1 = "=MEDIAN('Sheet1'!R[1]C[4]:R[4]C[4])"
4. Range("D2").Select
5. ActiveCell.FormulaR1C1 = "=MODE('Sheet1'!R[1]C[3]:R[4]C[3])"
6. Range("E2").Select
7. ActiveCell.FormulaR1C1 = "=QUARTILE('Sheet1'!R[1]C[2]:R[4]C[2],1)"
8. Range("F2").Select
9. ActiveCell.FormulaR1C1 = "=QUARTILE('Sheet1'!R[1]C[1]:R[4]C[1],3)"
10. Range("G2").Select
11. ActiveCell.FormulaR1C1 = "=GEOMEAN('Sheet1'!R[1]C:R[4]C)"
12. Range("G3").Select
13. End Sub
However, I am having trouble getting this to work (I am still getting the Runtime Error '1004').
I even tried the following, which runs without error but does not insert any of the values into the cells as directed:
1. Sub Stats()
2. Worksheets("Table").Activate
3. Range("C2").Value = "=MEDIAN('Sheet1'!G1:G6)"
4. Range("D2").Value = "=MODE('Sheet1'!G1:G6)"
5. Range("E2").Value = "=QUARTILE('Sheet1'!G1:G6,1)"
6. Range("F2").Value = "=QUARTILE('Sheet1'!G1:G6,3)"
7. Range("G2").Value = "=GEOMEAN('Sheet1'!G1:G6)"
8. Range("G3").Value = ""
9. End Sub
Any thoughts?? Should I just try to write all this in Python instead??
Thank you so much!

Nov 2nd, 2010, 10:03 AM
#4
Thread Starter
New Member
Re: [Excel] Workbook Function Error with Variables
I think I might have resolved the error, I was writing my code in a worksheet instead of a workbook module. That seems to perform the actions I am looking for without error. The next step will be to try and get my variables to work inside the functions...
Thank you all so much for your help!

Nov 2nd, 2010, 03:12 PM
#5
Re: [RESOLVED] [Excel] Workbook Function Error with Variables
I was writing my code in a worksheet instead of a workbook module
you should specify the sheet, by name or using a sheet object, to avoid error, rather than rely on the default or activesheet being the correct one
Should I just try to write all this in Python instead??
the same restrictions would apply, if automating excel, if you use code to do the calculations you can do the same in excel
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Tags for this Thread
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
