Results 1 to 9 of 9
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Minimum & Maximum Row Value

    I am trying to find out the minimum and maximum row value where in a declared worksheet Column 2 = strUnit (Unit name)



    I was attempting this code but I am getting:

    Runtime Error 424, Object required for:

    Code:
    MinCnt = varSheetB.DMin(strRngChk, 2, strUnit) 'WorksheetFunction
    MaxCnt = varSheetB.DMax(strRngChk, "Unit", strUnit)
    or

    Runtime Error 438, Object deosn't support this property or method

    Code:
    MinCnt = varSheetB.DMin(strRngChk, 2, strUnit) 'WorksheetFunction
    How can I go about finding the min and max row values? I am planning on using them for the for next count

    Code:
    For iRow = MinCnt To MaxCnt
    
         Next iRow
    Thanks


    Thanks

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    What type of object are you using here? Is it an Excel worksheet? If so, how are you using it in MS Access, i.e. through VBA, or as a linked table/spreadsheet?

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Through VBA

    Code:
    Private Sub comparetest_Click()
        Dim varSheetA As Variant
        Dim varSheetB As Variant
        Dim strRowType As String
        Dim strUnit As String
        Dim strRngChk As String
        Dim bFilesIdentical As Boolean
        Dim strVar As String
        Dim iRow As Long
        Dim iCol As Long
        Dim xlApp As Excel.Application
        Dim wkSheetA As Excel.Worksheet
        Dim wkSheetB As Excel.Worksheet
        Dim wkSheetC As Excel.Worksheet
        Dim WkBkA As Excel.Workbook
        Dim WkBkB As Excel.Workbook
        Dim WkBkC As Excel.Workbook
        Dim Inclr As Variant
        Dim TxtClr As Variant
        Dim BGC As Long
        Dim TC As Long
        Dim ColNum As Long
        Dim ColNum2 As Long
        Dim ClrCol As Long
        Dim MinCnt As Double
        Dim MaxCnt As Double
            
        ColNum = 36 And 38
        ColNum2 = 37 And 39
        'strRowType = "Plat"
        strUnit = "1-1 CAV SQDN"
        strRngChk = "A1:AQ30000"
    
           bFilesIdentical = True
           
    Set xlApp = New Excel.Application
           xlApp.Visible = False
           
    Set WkBkA = Workbooks.Open("C:\Temp\BalanceSheet_New.xlsx")
    Set WkBkB = Workbooks.Open("C:\Temp\BalanceSheet_old.xlsx")
    Set WkBkC = Workbooks.Open("C:\Temp\Comparison.xlsx")
    
    Set varSheetA = WkBkA.Worksheets(1).Range(strRngChk)
    Set varSheetB = WkBkB.Worksheets(1).Range(strRngChk)
    Set wkSheetC = WkBkC.Worksheets(1)
    
          ' WkBkA.Worksheets(1).Range(strRngChk).AutoFilter 8, strRowType
            WkBkA.Worksheets(1).Range(strRngChk).AutoFilter 2, strUnit
           'WkBkB.Worksheets(1).Range(strRngChk).AutoFilter 8, strRowType
            WkBkA.Worksheets(1).Range(strRngChk).AutoFilter 2, strUnit
    
           varSheetA = WkBkA.Worksheets(1).Range(strRngChk)
           varSheetB = WkBkB.Worksheets(1).Range(strRngChk)
        
    For iCol = 1 To 15
       wkSheetC.Cells(1, iCol) = varSheetA(1, iCol)
         wkSheetC.Cells(1, iCol).Interior.ColorIndex = 51
           wkSheetC.Cells(1, iCol).Font.ColorIndex = 2
            wkSheetC.Cells(1, iCol).Font.Bold = True
    Next iCol
         wkSheetC.Cells(1, 16) = "Changes Made"
         wkSheetC.Cells(1, 16).Interior.ColorIndex = 51
           wkSheetC.Cells(1, 16).Font.ColorIndex = 2
    
    MinCnt = WkBkB.DMin(strRngChk, 2, strUnit) 'WorksheetFunction
    MaxCnt = WkBkB.DMax(strRngChk, "Unit", strUnit)
             
             
        For iRow = MinCnt To MaxCnt

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    A quick Google search on excel worksheet functions in MS access vba turned up these, among others:


    http://archive.oreilly.com/pub/h/3310

    https://msdn.microsoft.com/en-us/lib...ffice.11).aspx


    The second one might be the most useful.

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I gather I must be going at it wrong. My intention is to find the lowest row where Column 2 on the worksheet = a string value, and the max row on that same value. .Dmin/.Dmax finds the smallest and largest values in that column. Would I need to do some kind of count??

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I changed the way I am going about it by using a count.

    But I get a Runtime error 1004, Application-defined or Object Defined error on the Red Bold letters

    Code:
     Do While stopSearch = False
         MinCnt = 0
        If varSheetB(MinCnt, 2) = strUnit Then
            stopSearch = True
        End If
        MinCnt = MinCnt + 1
      Loop
    MsgBox "Lowest " & MinCnt
    This works, but defeats what I am trying to do:
    Code:
    For MinCnt = 0 to 100
        If varSheetB(MinCnt, 2) = strUnit Then
    End If
    Next MinCnt
    MsgBox "Lowest " & MinCnt

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    If I understand correctly, you want to find, in a column, the first and last row where the cell contents contain a specified value. Something like this might work:

    Code:
    dim J as integer, K as Integer, MinCnt as Integer, MaxCnt as Integer
    
    MinCnt = 0
    MaxCnt = 0
    
    '
    ' Find first occurrance
    '
    for J = 1 to 100
      If varSheetB(J, 2) = strUnit Then
        mincnt = j
        exit for
    Next J
    
    '
    ' Find last row; logic is different
    '
    For K = J to 100
    If varSheetB(K, 2) = strUnit Then
      maxCnt = K
    Next K
    This will work if there is only one occurrence, or if there are none

  8. #8
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    John_G,
    I can get that to work, but I am want to avoid the for next as it sets a value. The spreadsheets I deal with can be in excess of 27K rows of data. I like the idea of the do while loop as it doesn't require a set start or end value. Its more like look for the first value required in Col 2 for minimum, then the last where it finds the final instance.

    It doesn't seem that it can find the object (worksheet) inside the loop. I think I have gotten somewhere with the below code. Also to note, there seems to be an issue where MinCnt = 0 where you get the Runtime error 91. If I change it to a 1, then it will run fine.

    Code:
    Do Until StopSrch = True
    If varSheetB(MinCnt, 2) = strUnit Then
            StopSrch = True
      End If
             MinCnt = MinCnt + 1
      Loop
    
    StopSrch = False
    MaxCnt = MinCnt
    
    Do Until StopSrch = True
    If varSheetB(MaxCnt, 2) <> strUnit Then
            StopSrch = True
      End If
             MaxCnt = MaxCnt + 1
      Loop

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Of course the code sets values - but isn't that what you want? You want to know where the first and last occurrences of a particular value are in a column.

    There isn't a lot of difference between s For .. Next loop and a Do While loop EXCEPT that you have to make sure there will always be a condition to terminate the DO WHILE loop.

    It doesn't seem that it can find the object (worksheet) inside the loop.
    Not likely. If you are still getting an error, the location of the object reference has nothing to do with whether the reference is inside or outside the loop. In you example above in red, it is more likely that the variable MinCnt contains an invalid value. Look at your code above - if this condition - varSheetB(MinCnt, 2) - is never true, then the loop will never terminate and you will get an error eventually.


    Put an error handler into your code, (on error goto...), so that in the event of a runtime error you can display or print the current contents of your variables to help determine where the error is.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-05-2015, 08:12 AM
  2. Minimum and Maximum Number
    By mohanmoni in forum Queries
    Replies: 3
    Last Post: 02-04-2015, 01:55 PM
  3. Minimum from muliple tables
    By inhops in forum Access
    Replies: 2
    Last Post: 05-23-2013, 10:58 PM
  4. Criteria for a minimum itself
    By hawkins in forum Access
    Replies: 17
    Last Post: 07-27-2011, 12:57 PM
  5. Can't see minimum or maximum buttons?!?
    By Felix_too in forum Forms
    Replies: 2
    Last Post: 12-29-2010, 10:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums