Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 36

Thread: Code needed for Max & Min Values
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    Try this version, which I think should address all your issues:
    Code:
    Private Sub Worksheet_Calculate()
    
        Dim cell As Range
        
    '   Exit if A1 not equal to 1
        If Range("A1") <> 1 Then Exit Sub
        
        Application.EnableEvents = False
        
    '   Loop through range of values that is being updated
        For Each cell In Range("B2:B10")
    '       Check/update Maximum
            If IsNumeric(cell.Offset(0, 3)) Then
                If cell > cell.Offset(0, 3) Then cell.Offset(0, 3) = cell
            Else
                cell.Offset(0, 3) = cell
            End If
    '       Check/update Minimum
            If IsNumeric(cell.Offset(0, 4)) Then
                If cell < cell.Offset(0, 4) Then cell.Offset(0, 4) = cell
            Else
                cell.Offset(0, 4) = cell
            End If
        Next cell
        
        Application.EnableEvents = True
    
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  2. #12
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    [QUOTE=Joe4;5340362]Try this version, which I think should address all your issues:

    Joe,
    First please allow me to say thanks to you for your time spared for my work.
    The last code DID NOT WORK.
    By default A1=0, & when A1 became 1

    1. B2:B10 was updating
    2. But E2:E10 retained “older” values that is previous day’s values
    3. F2:F10 DID NOT got lowest values (from B2:B10). It kept on showing blanks


    As per me:
    1 reason for E2:E10 not getting updated with highest values “could’ be: E2:E10 (when A1=0) contained previous day’s ‘higher’ values than the ones which were generated on current day in B2:B10 (when A1=1)

    I am stuck here.

    Astonishingly, why your code did not threw LOWEST in F2:F10 when A1=1. It kept on showing blanks

    Please help.

  3. #13
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    First please allow me to say thanks to you for your time spared for my work.
    The last code DID NOT WORK.
    By default A1=0, & when A1 became 1


    1. B2:B10 was updating
    2. But E2:E10 retained “older” values that is previous day’s values
    3. F2:F10 DID NOT got lowest values (from B2:B10). It kept on showing blanks
    Per you description here:
    as long as A1=1, B2:B10 should keep on throwing Max and min in E and F as soon as A1=0 it should stop throwing any values in E and F
    I read it to be as follows:
    1. If A1 is 1, columns E and F should continue to update
    2. If A1 is 0, columns E and F should stop updating, and be frozen to whatever values they are

    So, there is nothing resetting or clearing the values. A1 just dictates whether the values in E and F should continue to update, or be frozen to what they currently are.
    If you want to reset them somehow by code, you need to tell me how you want that to work.

    See if those code update fixes your blank issue:
    Code:
    Private Sub Worksheet_Calculate()
    
        Dim cell As Range
        
    '   Exit if A1 not equal to 1
        If Range("A1") <> 1 Then Exit Sub
        
        Application.EnableEvents = False
        
    '   Loop through range of values that is being updated
        For Each cell In Range("B2:B10")
    '       Check/update Maximum
            If (Len(cell.Offset(0, 3)) > 0) And (IsNumeric(cell.Offset(0, 3))) Then
                If cell > cell.Offset(0, 3) Then cell.Offset(0, 3) = cell
            Else
                cell.Offset(0, 3) = cell
            End If
    '       Check/update Minimum
            If (Len(cell.Offset(0, 4)) > 0) And (IsNumeric(cell.Offset(0, 4))) Then
                If cell < cell.Offset(0, 4) Then cell.Offset(0, 4) = cell
            Else
                cell.Offset(0, 4) = cell
            End If
        Next cell
        
        Application.EnableEvents = True
    
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #14
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    I read it to be as follows:
    1. If A1 is 1, columns E and F should continue to update YES YOU HAVE CORRECTLY UNDERSTOOD
    2. If A1 is 0, columns E and F should stop updating, and be frozen to whatever values they are YES YOU HAVE CORRECTLY UNDERSTOOD

    By default A1=0 & becomes 1 for a certain time period (30 minutes) during a time range

  5. #15
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    See if those code update fixes your blank issue:

    Is this code running in your trial Excel file? It may be that I am making some 'unknown' mistake/(s).
    Can you please upload / send 1 excel file with this code. It would certainly help me Joe4

  6. #16
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    If you want to reset them somehow by code, you need to tell me how you want that to work.

    Best would be:
    IF A2=z, 'put blanks in ALL cells of Highest (E2:E10) & Lowest columns (F2:F10) & STOP further ELSE
    CONTINUE with IF A1=1, start throwing Highest & Lowest in respective cells of the destination columns (& as per before criteria's)

  7. #17
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    IF A2=z, 'put blanks in ALL cells of Highest (E2:E10) & Lowest columns (F2:F10) & STOP further ELSE
    At this point, hopefully you should be able to start figuring some of this stuff out on your own (one of the reasons I add documentation is so that you can/see understand what I am doing, and hopefully learn some of it). Don't afraid to try it out for yourself - that is how you will learn!

    Try adding another block like I just did for A1 at the top of the previous code, to check for the other condition (put it before the A1 check so that it happens first). You will want this one to be an IF...THEN block (not a single line IF...THEN), as you will want it to first clear out the range E2:F10 before exiting the sub (so you will have two steps that happen if the condition is met).

    Try it out for yourself, and post back here with the your code attempt if you cannot get it to work.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #18
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    Try it out for yourself, and post back here with the your code attempt if you cannot get it to work.[/QUOTE]

    Joe4

    VBA is very difficult for me still I tried ...please check

    Private Sub Worksheet_Calculate()


    Dim cell As Range

    ' Exit if A2 not equal to z
    If Range("A2") = "z" Then
    Range("E2:F10").Select
    Selection.ClearContents
    Exit Sub


    Dim cell As Range

    ' Exit if A1 not equal to 1
    If Range("A1") <> 1 Then Exit Sub

    Application.EnableEvents = False

    ' Loop through range of values that is being updated
    For Each cell In Range("B2:B10")
    ' Check/update Maximum
    If (Len(cell.Offset(0, 3)) > 0) And (IsNumeric(cell.Offset(0, 3))) Then
    If cell > cell.Offset(0, 3) Then cell.Offset(0, 3) = cell
    Else
    cell.Offset(0, 3) = cell
    End If
    ' Check/update Minimum
    If (Len(cell.Offset(0, 4)) > 0) And (IsNumeric(cell.Offset(0, 4))) Then
    If cell < cell.Offset(0, 4) Then cell.Offset(0, 4) = cell
    Else
    cell.Offset(0, 4) = cell
    End If
    Next cell

    Application.EnableEvents = True


    End Sub

  9. #19
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    VBA is very difficult for me still I tried ...please check
    Understood, but this is the best way to learn!

    You were actually very close. You are just missing the "End If" statement, that is all.
    You can also combine the one line that ends in "Select" and the next that begins "Selection" (it usually is not necessary to select the ranges to work with them; the Macro Recorder is just very literal and records every action).
    Code:
    ' Exit if A2 not equal to z
    If Range("A2") = "z" Then
        Range("E2:F10").ClearContents
        Exit Sub
    End If
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #20
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    End If[/CODE][/QUOTE]

    The code works EXCEPT when A2="z" then it shows Run-time error '404'
    Comments please

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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