Offsetting a Range

[awright]

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

 

[Gary Keramidas]

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

 

[Joel]

It depends on what you I value is. If I - 1 is negative you will fail
because you rows start at 1. Offsetting Row 1 by -1 will get Row 0 which
will create an error.

 

[awright]

Thank you! You were completely right.

I gave it a good i and now it's working, but now of course another part isn't:

The last line:

MyCell.Offset(i - 1, 0).Value = Application.WorksheetFunction.index _
(IRange, 1, Application.WorksheetFunction.Match(Lvalue, Vrange,
False))

comes up with the error message: "Unable to get the Match property of the
WorksheetFunction class"

This is my first time using a WorksheetFunction, so I'm not sure what this
means.

Thanks for your help!

Alex