It appears that the MATCH function does not recognize the percentages
the same way the MAX function does. So that with 5%, 10%, 18%,3% and 6%
in CY12:CY16, the MAX function returns .18 but the MATCH function throws
an error, which turns into a 0 with the On Error Resume Next statement
in effect. So Range("CY12:CY16).Cells(FPos, 1) is interpreted as
Range("CY12:CY16").Cells(0,1), which refers to CY11.

Perhaps you can fool with the formatting or something so that the MATCH
function recognizes the match when percentages are used.

Alan Beban

Martin Wheeler wrote:
Hi Alan,
The values are all %'s and the On Error Resume Next is in effect.
I tried turning the On Error off but it then seems to skip the code.
Any ideas?
Ta,
Martin


"Alan Beban" wrote in message
...

I think the only way that Martin's asserted result can be reproduced
with something like Tom Ogilvy's code is i) there are no numeric values
in CY12:CY16, and 2) an On Error Resume Next statement is in effect.

Alan Beban

Martin Wheeler wrote:

Hi Tom
I've tried your code but it returns CY11 which is one above the range.

It

should select CY13.
Any ideas?
Ta,
Martin


"Tom Ogilvy" wrote in message
...


Dim F As Single
Dim FPos As Long
Dim FRng As Range
F = Application.WorksheetFunction.Max(.Range("CY12:CY1 6"))
FPos = WorksheetFunction.Match(F, .Range("CY12:CY16"), 0)
Set FRng = .Range("CY12:CY16").Cells(FPos,1)
FRng.Select

--
Regards,
Tom Ogilvy


"Martin Wheeler" wrote in message
.. .


XL2000

I am trying to use the match function, by modifying some existing code

.

The original code is for a row but the new code is for a column. The
problem is that is not selecting the right cell. Instead it is

selecting


the cell one column to the left and up one. I have tried playing with

the


settings but cannot get it too select the cell I want. Below is the

code


I


am using. Any help would be greatly appreciated.

Dim F As Single
Dim FPos As Long
Dim FRng As Range
F = Application.WorksheetFunction.Max(.Range("CY12:CY1 6"))
FPos = WorksheetFunction.Match(F, .Range("CY12:CY16"), 0)
Set FRng = .Range("CY12:CY16").Cells(1, FPos)
FRng.Select

It should select CY13 but it select CX12
Ta,
Martin