+ Reply to Thread
Results 1 to 4 of 4

Max function VBA

Hybrid View

amotto11 Max function VBA 06-05-2012, 09:42 AM
zbor Re: Max function VBA 06-05-2012, 09:45 AM
amotto11 Re: Max function VBA 06-05-2012, 09:47 AM
JieJenn Re: Max function VBA 06-05-2012, 09:47 AM
  1. #1
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Max function VBA

    HI all,

    This should be really easy but i am very new to VBA, I am looking at line 9. What i would like this to be is the max of this number or 57, something like
    TopRow = Max(57, .Range("K13").Value / 3)
    Here is my code, Thanks for all of your help

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim NewRange    As String, _
            TopRow      As Long
            
        If EditMode = ActiveSheet.Name Then Exit Sub
        
        With ActiveSheet
            If .Range("I8").Value <> "" And .Range("I9").Value <> "" Then
                TopRow = .Range("K13").Value / 3
                NewRange = Cells(56 - TopRow, "C").Address(0, 0) & ":C56"
                .Unprotect "viceroy11"
                .Cells.Locked = True
                .Cells.Interior.ColorIndex = xlColorIndexNone
                .Range("I8:I11").Locked = False
                .Range("I13:I15").Locked = False
                .Range("N28:O53").Locked = False
                .Range("I20").Locked = False
                .Range("Q37").Locked = False
                .Range(NewRange).Interior.ColorIndex = 6
                .Range(NewRange).Locked = False
                .Protect "viceroy11"
                .EnableSelection = xlUnlockedCells
                .Protect
                EditMode = ActiveSheet.Name
            End If
        End With
    exit_routine:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,058

    Re: Max function VBA

    Maybe:

    TopRow = Application.Max(57, .Range("K13").Value / 3)
    Never use Merged Cells in Excel

  3. #3
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Max function VBA

    That worked, thank you so much!

  4. #4
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Max function VBA

    Try

    TopRow = WorksheetFunction.Max(57, .Range("K13").Value / 3)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1