max of x values and offset

earp_

Active Member
Joined
Apr 30, 2008
Messages
305
Hi,
if I want to find the max of 5 cells (every cell is filled every sec) from A1 to A65536

I write
If (myCount Mod 5 = 0) Then
With Worksheets("Sheet2")
Worksheets("MaxMin").Range("B65536").End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Max(.Range(.Range("A65536").End(xlUp), .Range("A65536").End(xlUp).Offset(-5)))
Worksheets("MaxMin").Range("C65536").End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Min(.Range(.Range("A65536").End(xlUp), .Range("A65536").End(xlUp).Offset(-5)))
End With
End If
but if the first 5 cells are empty I received an error.
The only solution I found is to put a value (ex. 0 ) inside the first 5 cells.
What if I don't want to initialize the first 5 cell?

Count goes from 1 to infinite and updates every 1 sec
MaxMin is the sheet where I save the Max and the Min every 5 sec from Sheet2
Sheet2 is updated every 1 sec from A1 to A65536
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I presume you get error in MIN code
in my version excel 2002 it is ok

see the experimental sheet given below and on that run the macro given below
Code:
Sub test()
Dim k As Integer
k = WorksheetFunction.Min(Range(Range("a65536").End(xlUp), Range("a65536").End(xlUp).Offset(-5, 0)))
MsgBox k
End Sub
I get the result 1
Book1
ABCD
1
2
3
4
5
64
71
8
9
Sheet1
 
Upvote 0
the error is for Max because it executes that first.
But I thought that

If (myCount Mod 5 = 0) Then

end IF
gave me the possibility to execute the code when myCount is multiple of 5, instead it looks like it goes inside also at the first loop when myCount = 0
 
Upvote 0
I think your data is in sheet2. and you wanat to copy the max and min in the sheet "maxmin" is it ok??? remove the if statement and end if a t the end. the macro will be
Code:
Sub test()


'If (myCount Mod 5 = 0) Then
Dim k
With Worksheets("Sheet2")
k = WorksheetFunction.Max(Range(.Range("A65536").End(xlUp), .Range("A65536").End(xlUp).Offset(-5)))

Worksheets("MaxMin").Range("B65536").End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Max(Range(.Range("A65536").End(xlUp), .Range("A65536").End(xlUp).Offset(-5, 0)))
Worksheets("MaxMin").Range("C65536").End(xlUp).Offset(1, 0) = Application.WorksheetFunction.Min(Range(.Range("A65536").End(xlUp), .Range("A65536").End(xlUp).Offset(-5, 0)))
End With
'End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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