# max of x values and offset

#### earp_

##### Active Member
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### venkat1926

##### Well-known Member
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

#### earp_

##### Active Member
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

#### venkat1926

##### Well-known Member
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``````

fix it tks

Replies
7
Views
89
Replies
2
Views
140
Replies
6
Views
97
Replies
6
Views
242
Replies
3
Views
81

1,191,693
Messages
5,988,136
Members
440,128
Latest member
virginia356

### 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?

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