Code needed for Max & Min Values

[hsandeep]

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

	B	C	D	E	F
			MAXIMUM	MINIMUM

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]458.45[/TD]

[TD="align: right"]458.45[/TD]
[TD="align: right"]451.23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]470.26[/TD]

[TD="align: right"]470.38[/TD]
[TD="align: right"]459.22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]280.01[/TD]

[TD="align: right"]285.98[/TD]
[TD="align: right"]260.01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]510.05[/TD]
[TD="align: right"]492.06[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]9999[/TD]

[TD="align: right"]10000[/TD]
[TD="align: right"]9874.2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]2222[/TD]

[TD="align: right"]2255[/TD]
[TD="align: right"]2220[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]1111[/TD]

[TD="align: right"]1181[/TD]
[TD="align: right"]1111[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]1090[/TD]

[TD="align: right"]1090[/TD]
[TD="align: right"]1090[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]7755.33[/TD]

[TD="align: right"]775.33[/TD]
[TD="align: right"]7755.3[/TD]

</tbody>

 

[Joe4]

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?

 

[hsandeep]

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]

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]

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]

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]

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

The code WAS in 2 parts
1. 1^st part: E2:F10 grabbing HIGHEST & LOWEST from B2:B10 when A1=1
2. 2^nd part: Clearing all values from E2:F10 & ‘filing’ blank when A2=z
3. 1^st part WORKS WELL
4. 2^nd 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 2^nd part to ‘your’ code.
Please ‘add’ the 2^nd part to your code ALSO………this may solve the remaining last problem.

Thanks

 

[Joe4]

Please ‘add’ the 2^nd part to your code ALSO………this may solve the remaining last problem.

Didn't we already do that?

4. 2^nd 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]

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]

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]

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.