Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 37

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

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

    Default Re: Code needed for Max & Min Values

    What is exact text of the error message that is being returned (I believe 404 errors can mean different things, and can have different error messages)?
    And when you hit "debug" when that error occurs, which line of code is highlighted?
    Last edited by Joe4; Sep 18th, 2019 at 08:53 AM.
    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. #22
    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 Originally Posted by Joe4 View Post
    What is exact text of the error message that is being returned (I believe 404 errors can mean different things, and can have different error messages)?
    And when you hit "debug" when that error occurs, which line of code is highlighted?
    Joe4

    The Code WORKS EXCEPT for: (trying to explain)
    1. I have inserted 13 Worksheets in 1 Excel file with the SAME code (after ADDING A2=”z” criteria) in ALL the 13 Worksheets.
    2. A2 became z in all 13 Worksheets at the SAME time (since A2 contains formula related with time)
    3. Till this moment, the code WORKED FINE.
    4. THEREAFTER (when A2 became z in all 13 Worksheets at the SAME time), system ‘hanged’
    5. WS1 was okay HAVING removed all values from E2:F10 & kept blanks
    6. But WS2:WS13 DID NOT REMOVED at all values from E2:F10 (despite A2=z)
    7. When debugged, message was: Run-time error ‘404’
    8. Private Sub Worksheet_Calculate()was highlighted with yellow background color



    Comments please

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

    Default Re: Code needed for Max & Min Values

    Did you place this code in EVERY sheet module?

    Perhaps there are conflicts, if they are all changing at the same time, that you have 13 sets of code trying to run simulateneously.
    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. #24
    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 Originally Posted by joe4 View Post
    did you place this code in every sheet module? yes

    perhaps there are conflicts, if they are all changing at the same time, that you have 13 sets of code trying to run simulateneously.
    what to do?

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

    Default Re: Code needed for Max & Min Values

    I really don't know. I have never tried to create anything like that before, where you have 13 sheets being updated by links simultaneously, and having 13 VBA event procedures running simultaneously.
    Without having access to your file and system, it is very hard to try to debug it from this side.
    Its possible that you may need to re-think the design of this.

    At this point, your best bet may be to enlist the help of some consulting services, such as the ones listed here: https://www.mrexcel.com/consulting-services/
    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!"

  6. #26
    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

    Its possible that you may need to re-think the design of this.

    The code WAS in 2 parts
    1. 1st part: E2:F10 grabbing HIGHEST & LOWEST from B2:B10 when A1=1
    2. 2nd part: Clearing all values from E2:F10 & ‘filing’ blank when A2=z
    3. 1st part WORKS WELL
    4. 2nd part DID NOT WORKED…….however it WORKED only in WS1 & not in other WS2:W13

    I think there could be some “mistake” in my ‘adding’ the 2nd part to ‘your’ code.
    Please ‘add’ the 2nd part to your code ALSO………this may solve the remaining last problem.

    Thanks

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

    Default Re: Code needed for Max & Min Values

    Please ‘add’ the 2nd part to your code ALSO………this may solve the remaining last problem.
    Didn't we already do that?

    4. 2nd part DID NOT WORKED…….however it WORKED only in WS1 & not in other WS2:W13
    That is the part that does not make sense to me, that it only works on one sheet. However, you did not initially mention that you had this code copied on 13 sheets!

    If you want to post the code, as you currently have it, I would be willing to look it over to see if anything stands out. But if nothing does, I really do not know where to go from there.
    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. #28
    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 post the code, as you currently have it, I would be willing to look it over to see if anything stands out. But if nothing does, I really do not know where to go from there.[/QUOTE] Code 'MODIFIED/ADDED' by me as:

    Private Sub Worksheet_Calculate()


    Dim cell As Range


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


    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. #29
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    I don't see anything in the code itself that would be causing issues.
    I think it is more the interaction of having 13 copies of this code running simultaneously.
    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. #30
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,814
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    I had mentioned earlier that you may need to re-think your design of this. If it were me, I would try to get rid of the situation where you have 13 copies of the code running simultaneously.
    Options for that include:
    1. Putting everything on one sheet instead of 13 sheets.
    2. Have 13 different files with one sheet instead of 1 file with 13 sheets.

    Don't know if either of those options is feasible, but those are some things to consider.
    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!"

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
  •