Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match 2 Columns, Return 3rd, Differing Match Types | Excel Worksheet Functions | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |