Match
 

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

Match
 

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

Match
 

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

Match
 

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

Match
 

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

Alan Beban

Alan Beban wrote:
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

Match
 

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

Match
 

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


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

Alan Beban

Alan Beban wrote:
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

Match
 

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

Match
 

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.)



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





--

Dave Peterson

Match
 

This worked ok in my limited testing.


Dim F As Double



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







--

Dave Peterson

Match
 

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

I'll use Double and Long.



Dave Peterson wrote:

This worked ok in my limited testing.

Dim F As Double

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







--

Dave Peterson

--

Dave Peterson

Match
 

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


"Dave Peterson" wrote in message
...
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.)



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





--

Dave Peterson

Match
 

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


"Dave Peterson" wrote in message
...
BTW, I've stopped using Single and Integer in my code.

I'll use Double and Long.



Dave Peterson wrote:

This worked ok in my limited testing.

Dim F As Double

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







--

Dave Peterson

--

Dave Peterson

Match
 

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


"Alan Beban" wrote in message
...
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