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

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

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

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