Previous Topic Next Topic

Can't get worksheetfunction to work

Closed
Brad - May 26, 2009 at 12:59 PM
 Brad - May 28, 2009 at 08:32 AM
Hello,

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 responses

Answer 1 / 2
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
May 28, 2009 at 02:35 AM
Hi

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...
0
Answer 2 / 2
Brad
May 28, 2009 at 08:32 AM
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.
0