Match

[Martin Wheeler]

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:CY16"))
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

 

[Tom Ogilvy]

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

 

[Martin Wheeler]

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

 

[Alan Beban]

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

 

[Alan Beban]

Or if there's an error value in CY12:CY16 and an On Error Resume Next
statement in effect.

Alan Beban

 

[Martin Wheeler]

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

 

[Martin Wheeler]

No there are no error values in CY12:CY16
Ta,
Martin

 

[Alan Beban]

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

 

[Dave Peterson]

you have .range("cy12:cy16") in your code.

What's the corresponding with statement?

is it
with activesheet
?

==
Tom's code picked out the correct cell (with the largest number) for me.

(You do have numbers--not text masquerading as numbers in that range? If you
have that situation and "on error resume next", I could duplicate your problem.)

 

[Dave Peterson]

This worked ok in my limited testing.


Dim F As Double

 

[Dave Peterson]

BTW, I've stopped using Single and Integer in my code.

I'll use Double and Long.

 

[Martin Wheeler]

Hi Dave,
Sorry to be so long in getting back.
I am using Dim wks As Worksheet if this makes any difference
Ta,
Martin

 

[Martin Wheeler]

Hi Dave,
Sorry to be so long in getting back but I was having to run the software and
I only have 1 computer. It takes about 2hrs a day so I could not try the
changes.
In any event I switched from single to double for 'F' and it works great.
Thanks for your help.
Ta,
Martin

 

[Martin Wheeler]

Hi Alan,
Sorry to be so long in getting back but I was having to run the software and
I only have 1 computer. It takes about 2hrs a day so I could not try the
changes.
In any event I switched from single to double for 'F', as suggested by Dave,
and it works great.
Thanks for your help.
Ta,
Martin