Can't get worksheetfunction to work

 Brad -

i have a column of data where i am trying to find the maximum for each 96 values. you'll understand this better when you see my macro below. i am trying to figure how to use a worksheet function so i can find the maximum of these numbers. i want variables n and m to change obviously and its difficult to do when specifying a certain cell so i decided to use row number's instead cell addresses. all the other syntax works i just can't get this worksheetfunction to execute. any help would be great thanks.

Sub Loop6()
Dim lastrow As Long
Dim d As Integer
lastrow = [B65536].End(xlUp).Row
d = ((lastrow - 3) / 95) - 1
n = ActiveCell.Row
m = ActiveCell.Offset(95, 0).Row
For i = 1 To d
l = WorksheetFunction.Max(Range(n, m))
n = n + 96
m = m + 96
Next i
MsgBox l
End Sub

2 replies

Registration date
Saturday April 11, 2009
Last seen
June 21, 2011

Try using WorksheetFunction.Max(Range(Cells(n, Column), Cells(m, Column))) instead of WorksheetFunction.Max(Range(n, m))

Do let me know If it helps...
yes it works thank you! now i want to print each of those numbers in excel. so for each day print the number into excel and then offset down a row and print the next. could i just do

Range("C3").Offset(1,0).Value = l

i think i've tried something similar with no luck.