[RESOLVED] [Excel] Workbook Function Error with Variables

[funkykizzy]

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:

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!

[westconn1]

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

[funkykizzy]

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!

[funkykizzy]

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!

[westconn1]

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