Code needed for Max & Min Values

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
I have B2:B10 which are dynamic cells & keeps on UPDATING (based on time criteria)
Corresponding MAXIMUM & MINIMUM values required in E2:E10 & F2:F10
Code required to accomplish.
I am using Excel 2007.
Thanks in adv.
Sheet1

BCDEF
1 MAXIMUMMINIMUM
2458.45 458.45451.23
3470.26 470.38459.22
4280.01 285.98260.01
5500 510.05492.06
69999 100009874.2
72222 22552220
81111 11811111
91090 10901090
107755.33 775.337755.3

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 73px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>
 
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:
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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/
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top