[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!