Results 1 to 5 of 5

Thread: [RESOLVED] [Excel] Workbook Function Error with Variables

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Location
    Durham, NC
    Posts
    5

    Resolved [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 ;)

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,900

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Location
    Durham, NC
    Posts
    5

    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!

  4. #4

    Thread Starter
    New Member
    Join Date
    Nov 2010
    Location
    Durham, NC
    Posts
    5

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

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,900

    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
  •  



Click Here to Expand Forum to Full Width