locked
VB: maybe a small syntax problem ?? RRS feed

  • Question

  • pardon,  working on this for a few hours,  with other changes done in between?  seems the next line allowed it to work:

       ' Set OMG = ActiveWindow.Panes(ActiveWindow.Panes.Count).VisibleRange    'this is working:

    'if not the same as below,  working as:

       'excel.Workbooks.application.WindowState = XlWindowState.xlMinimized   '<< CK TEST  'no bounce to wb
        Dim findMAX As Integer, OMG As RANGE: Set OMG = ActiveWindow.Panes(ActiveWindow.Panes.Count).VisibleRange
        findMAX = application.Max(RANGE(Cells(OMG.row + 2, "N:N"), Cells(Rows.Count, Cells(OMG.row + 2, "N:N").Column).End(xlUp)))

        MsgBox findMAX & Space(10), vbQuestion
    'YES:
        'MsgBox application.Max(RANGE(Cells(activecell.row, "N:N"), Cells(Rows.Count, Cells(activecell.row, "N:N").Column).End(xlUp))) & (space10), vbQuestion   'YES

    ==========

    hi,  i have a couple lf lines that show work in the first (bottom) example,  but cannot get to work if try to set the variables?  not sure if wording that correctly.  am very slow at vb,  but guessing just some syntax problem.  thanks in advance.

    what this is for is to find the max value in a column, as part of a search / FIND.  the convenience of being able to search from the current view down is necessary  (from current view is first choice: visible panes;   from selection.row 2nd choice).  what have:

     

        excel.Workbooks.application.WindowState = XlWindowState.xlMinimized   '<< CK TEST  'yes: use to keep from bouncing to wb & back again

        Dim findMAX As Integer, OMG As RANGE
        Set OMG = Rows(ActiveWindow.Panes(ActiveWindow.Panes.Count).VisibleRange).row   'err why, .row = type mismatch (err went away)
         
    'NO:
        findMAX = application.Max(RANGE(Cells(OMG.row, "N:N"), Cells(Rows.Count, Cells(OMG.row, "N:N").Column).End(xlUp)))    'NO
        'findMAX = application.Max(RANGE(Cells(activecell.row, "N:N"), Cells(Rows.Count, Cells(activecell.row, "N:N").Column).End(xlUp)))   'NO?  findmax=  obj required
            'NO?  findmax=  obj required    'ANS IS?: removed word "set" from find max
        MsgBox findMAX & Space(10), vbQuestion
     
    'YES:
        'MsgBox application.Max(RANGE(Cells(activecell.row, "N:N"), Cells(Rows.Count, Cells(activecell.row, "N:N").Column).End(xlUp))) & (space10), vbQuestion   'YES








    • Edited by Davexx Wednesday, August 16, 2017 4:56 AM
    Wednesday, August 16, 2017 2:56 AM

All replies

  • what this is for is to find the max value in a column, as part of a search / FIND.  the convenience of being

    Well, you have to learn how to refer to a Range object correctly, anything else after that is easy (and always the same).

    Make a new file and setup the cells this picture shows:

    So what is the min/max/average of cells A3,A5,A7,A9 resp. A2:A8, resp. all that have an "x" in column B?

    If you try to answer that with a code in the way you write it as shown above, you really have a problem and will end up with many lines. I need just a few in my main routine.

    Andreas.

    Option Explicit
    
    Sub Test()
      Dim R As Range
      'Single cells
      Set R = Range("A3,A5,A7,A9")
      GoSub Show
      
      'From cell, to cell
      Set R = Range("A2", "A8")
      GoSub Show
      
      'Find all "x" in column B
      Set R = FindAll(Columns("B"), "x")
      If R Is Nothing Then
        Debug.Print "No 'x' in B"
        Exit Sub
      End If
      'Refer to the cells in column A
      Set R = R.Offset(, -1)
      GoSub Show
      
      Exit Sub
    Show:
      With WorksheetFunction
        Debug.Print
        Debug.Print "Min", .Min(R)
        Debug.Print "Max", .Max(R)
        Debug.Print "Average", .Average(R)
      End With
      Return
    End Sub
    
    Function FindAll(ByVal Where As Range, ByVal What, _
        Optional ByVal After As Variant, _
        Optional ByVal LookIn As XlFindLookIn = xlValues, _
        Optional ByVal LookAt As XlLookAt = xlWhole, _
        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
        Optional ByVal MatchCase As Boolean = False, _
        Optional ByVal SearchFormat As Boolean = False) As Range
      'Find all occurrences of What in Where (Windows version)
      Dim FirstAddress As String
      Dim c As Range
      'From FastUnion:
      Dim Stack As New Collection
      Dim Temp() As Range, Item
      Dim i As Long, j As Long
    
      If Where Is Nothing Then Exit Function
      If SearchDirection = xlNext And IsMissing(After) Then
        'Set After to the last cell in Where to return the first cell in Where in front if _
          it match What
        Set c = Where.Areas(Where.Areas.Count)
        'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
        'Set After = C.Cells(C.Cells.Count)
        Set After = c.Cells(c.Rows.Count * CDec(c.Columns.Count))
      End If
    
      Set c = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
      If c Is Nothing Then Exit Function
    
      FirstAddress = c.Address
      Do
        Stack.Add c
        If SearchFormat Then
          'If you call this function from an UDF and _
            you find only the first cell use this instead
          Set c = Where.Find(What, c, LookIn, LookAt, SearchOrder, _
            SearchDirection, MatchCase, SearchFormat:=SearchFormat)
        Else
          If SearchDirection = xlNext Then
            Set c = Where.FindNext(c)
          Else
            Set c = Where.FindPrevious(c)
          End If
        End If
        'Can happen if we have merged cells
        If c Is Nothing Then Exit Do
      Loop Until FirstAddress = c.Address
    
      'FastUnion algorithm © Andreas Killer, 2011:
      'Get all cells as fragments
      ReDim Temp(0 To Stack.Count - 1)
      i = 0
      For Each Item In Stack
        Set Temp(i) = Item
        i = i + 1
      Next
      'Combine each fragment with the next one
      j = 1
      Do
        For i = 0 To UBound(Temp) - j Step j * 2
          Set Temp(i) = Union(Temp(i), Temp(i + j))
        Next
        j = j * 2
      Loop Until j > UBound(Temp)
      'At this point we have all cells in the first fragment
      Set FindAll = Temp(0)
    End Function
    
    


    Wednesday, August 16, 2017 10:14 AM
  • Hi Davexx,

    ->Set OMG = Rows(ActiveWindow.Panes(ActiveWindow.Panes.Count).VisibleRange).row

    I think there is no such syntax like Rows(Range). As you shared in first part, you could use OMG.Row to get the start row index in current view.

    It seems that you want to find max value in column N from third row in current view to the end, right?

    Here is the example.

    Dim findMAX As Integer, OMG As Range

        Set OMG = ActiveWindow.Panes(ActiveWindow.Panes.Count).VisibleRange '

        startRowIndex = OMG.Row + 2

        'Column is 14th in Columns

        endRowIndex = Cells(Rows.Count, 14).End(xlUp).Row

        Dim startCell As Range

        Dim encCell As Range

        Set startCell = Cells(startRowIndex, 14)

        Set endCell = Cells(endRowIndex, 14)

        searchRange = Range(startCell, endCell)

        findMAX = Application.Max(searchRange)

       MsgBox findMAX & Space(10), vbQuestion

    Best Regards,

    Terry

    Thursday, August 17, 2017 7:37 AM