Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match WorksheetFunction Question
This is my first time using a WorksheetFunction, so I'm not sure what this error message means. The problem code is: MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _ (IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange, _ False)) ....and it comes up with the error message: "Unable to get the Match property of the WorksheetFunction class" Thanks for your help! Alex P.S. This is a continuation of a previous question - for Gary: Thank you! You were completely right. "Gary Keramidas" wrote: it looks like it's doing what you want: type or paste these 3 lines in the immediate window , pressing enter after each one, and see what the range is for irange i =5 Set IRange = Sheets("Historical Load").Range("C1:BA1").Offset(i - 1,0) ?irange.address then change the value of i and try again. immediate window is accessed by Control-G or view/immediate window from the menu in the vb editor -- Gary "awright" wrote in message ... Here's what I have so far: Lvalue = Sheets("Historical Load").Range("A2").Value Set IRange = Sheets("Historical Load").Range("C1:BA1").Offset(i - 1, 0) Set Vrange = Sheets("Historical Load").Range("C8:BA8") Set MyCell = Sheets("Nomination").Range("H1") MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _ (IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange, False)) This is part of a For - Next loop, where i is an integer. In that second row, I want to Offset that entire range ("C1:BA1") by the amount i - 1. I think it's not working because Offset is usually for a single cell. Does anyone have any suggestions for getting this to work? Thanks! Alex |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match WorksheetFunction Question
It is because Match can not find Lvalue, in the Vrange. Confirm that Lvalue
is in Vrange. Hope this helps! If so, let me know and click "YES" below. -- Cheers, Ryan "awright" wrote: This is my first time using a WorksheetFunction, so I'm not sure what this error message means. The problem code is: MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _ (IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange, _ False)) ...and it comes up with the error message: "Unable to get the Match property of the WorksheetFunction class" Thanks for your help! Alex P.S. This is a continuation of a previous question - for Gary: Thank you! You were completely right. "Gary Keramidas" wrote: it looks like it's doing what you want: type or paste these 3 lines in the immediate window , pressing enter after each one, and see what the range is for irange i =5 Set IRange = Sheets("Historical Load").Range("C1:BA1").Offset(i - 1,0) ?irange.address then change the value of i and try again. immediate window is accessed by Control-G or view/immediate window from the menu in the vb editor -- Gary "awright" wrote in message ... Here's what I have so far: Lvalue = Sheets("Historical Load").Range("A2").Value Set IRange = Sheets("Historical Load").Range("C1:BA1").Offset(i - 1, 0) Set Vrange = Sheets("Historical Load").Range("C8:BA8") Set MyCell = Sheets("Nomination").Range("H1") MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _ (IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange, False)) This is part of a For - Next loop, where i is an integer. In that second row, I want to Offset that entire range ("C1:BA1") by the amount i - 1. I think it's not working because Offset is usually for a single cell. Does anyone have any suggestions for getting this to work? Thanks! Alex |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match WorksheetFunction Question
Ryan,
I copied a value directly from the Vrange into A2, and it still didn't work. Could it be another problem? Thank you! Alex "RyanH" wrote: It is because Match can not find Lvalue, in the Vrange. Confirm that Lvalue is in Vrange. Hope this helps! If so, let me know and click "YES" below. -- Cheers, Ryan "awright" wrote: This is my first time using a WorksheetFunction, so I'm not sure what this error message means. The problem code is: MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _ (IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange, _ False)) ...and it comes up with the error message: "Unable to get the Match property of the WorksheetFunction class" Thanks for your help! Alex P.S. This is a continuation of a previous question - for Gary: Thank you! You were completely right. "Gary Keramidas" wrote: it looks like it's doing what you want: type or paste these 3 lines in the immediate window , pressing enter after each one, and see what the range is for irange i =5 Set IRange = Sheets("Historical Load").Range("C1:BA1").Offset(i - 1,0) ?irange.address then change the value of i and try again. immediate window is accessed by Control-G or view/immediate window from the menu in the vb editor -- Gary "awright" wrote in message ... Here's what I have so far: Lvalue = Sheets("Historical Load").Range("A2").Value Set IRange = Sheets("Historical Load").Range("C1:BA1").Offset(i - 1, 0) Set Vrange = Sheets("Historical Load").Range("C8:BA8") Set MyCell = Sheets("Nomination").Range("H1") MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _ (IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange, False)) This is part of a For - Next loop, where i is an integer. In that second row, I want to Offset that entire range ("C1:BA1") by the amount i - 1. I think it's not working because Offset is usually for a single cell. Does anyone have any suggestions for getting this to work? Thanks! Alex |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match WorksheetFunction Question
What's in lValue?
Is it a date? If yes, then maybe using clng(lvalue) would work better. Could it be some sort of rounding error that is hidden by the number formatting? ===== By the way, I'd do this: Dim res as variant dim myCell as range dim vRange as range dim iRange as range dim i as long 'assign some values/ranges... res = application.match(lvalue,vrange, 0) if iserror(Res) then msgbox "no match" else mycell.offset(i-1,0).value = irange.cells(1).offset(1,res) end if === (I think I did the application.worksheetfunction.index translation ok--but test it out!) === If you use .worksheetfunction, and there is no match, you'll get a runtime error. If I use application.vlookup() and there is no match, then it'll return an error that I can check for. And I find that easier to code. awright wrote: Ryan, I copied a value directly from the Vrange into A2, and it still didn't work. Could it be another problem? Thank you! Alex "RyanH" wrote: It is because Match can not find Lvalue, in the Vrange. Confirm that Lvalue is in Vrange. Hope this helps! If so, let me know and click "YES" below. -- Cheers, Ryan "awright" wrote: This is my first time using a WorksheetFunction, so I'm not sure what this error message means. The problem code is: MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _ (IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange, _ False)) ...and it comes up with the error message: "Unable to get the Match property of the WorksheetFunction class" Thanks for your help! Alex P.S. This is a continuation of a previous question - for Gary: Thank you! You were completely right. "Gary Keramidas" wrote: it looks like it's doing what you want: type or paste these 3 lines in the immediate window , pressing enter after each one, and see what the range is for irange i =5 Set IRange = Sheets("Historical Load").Range("C1:BA1").Offset(i - 1,0) ?irange.address then change the value of i and try again. immediate window is accessed by Control-G or view/immediate window from the menu in the vb editor -- Gary "awright" wrote in message ... Here's what I have so far: Lvalue = Sheets("Historical Load").Range("A2").Value Set IRange = Sheets("Historical Load").Range("C1:BA1").Offset(i - 1, 0) Set Vrange = Sheets("Historical Load").Range("C8:BA8") Set MyCell = Sheets("Nomination").Range("H1") MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _ (IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange, False)) This is part of a For - Next loop, where i is an integer. In that second row, I want to Offset that entire range ("C1:BA1") by the amount i - 1. I think it's not working because Offset is usually for a single cell. Does anyone have any suggestions for getting this to work? Thanks! Alex -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheetfunction.match | Excel Programming | |||
Re : Excel VBA and WorkSheetFunction (Match) | Excel Programming | |||
Syntax for WorksheetFunction Match | Excel Programming | |||
worksheetfunction.match | Excel Programming | |||
Worksheetfunction MATCH | Excel Programming |