Refer to a range using cells instead of Range

[tiredofit]

This works:

MaxVal = Application.WorksheetFunction.Max(Sheet1.Range("A1:F1"))

but not this:

MaxVal = Application.WorksheetFunction.Max(Range(Cells(1,1), Cells(1,6))))

neither do these:

MaxVal = Application.WorksheetFunction.Max(Range(Sheet1.Cells(1,1), Sheet1.Cells(1,6))))
MaxVal = Application.WorksheetFunction.Max(Sheet1.Range(Cells(1,1), Cells(1,6))))
MaxVal = Application.WorksheetFunction.Max(Sheet1.Range(Sheet1.Cells(1,1), Sheet1.Cells(1,6))))

Why is that?

Thanks

 

[steve the fish]

Well you have one too many bracket but aside from that they all work if you are on Sheet1 and the first and last in the group of 3 does if you arent.

 

[Fluff]

This would work

[COLOR=#222222][FONT=Verdana]MaxVal = Application.WorksheetFunction.Max(Sheet1.Range(Sheet1.Cells(1,1), Sheet1.Cells(1,6))))[/FONT][/COLOR]

except you have 1 to many ) at the end

 

[Peter_SSs]

Why is that?

All the failing ones have syntax errors (an extra closing parenthesis at the end)

Even after that correction, at least one will fail if Sheet1 is not the active sheet because an un-referenced range will apply to the active sheet.

 

[tiredofit]

Thanks to all.

My objective is NOT to be on Sheet1 when the code runs.

Agree the last in the set of three works but not the first.

Not a problem, as long as I've got one that does work.

 

[steve the fish]

This one doesnt?

MaxVal = Application.WorksheetFunction.Max(Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, 6)))

It does on my machine even if Sheet1 isnt the active sheet.

 

[RoryA]

This one doesnt?

MaxVal = Application.WorksheetFunction.Max(Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, 6)))

It does on my machine even if Sheet1 isnt the active sheet.

That should work as long as the code is not in another worksheet's code module. You should really qualify the Range property too though.

In a worksheet code module, Range or Cells with no qualifier is equivalent to Me.Range and Me.Cells; anywhere else, it is equivalent to Application.Range or Application.Cells