Code needed for Max & Min Values

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,164
Office Version
365
Platform
Windows
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:

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,164
Office Version
365
Platform
Windows
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.
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,164
Office Version
365
Platform
Windows
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/
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,164
Office Version
365
Platform
Windows
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.
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,164
Office Version
365
Platform
Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,164
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,078,337
Messages
5,339,632
Members
399,316
Latest member
LeroyWork

Some videos you may like

This Week's Hot Topics

Top