Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting a Range
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
|
|||
|
|||
Offsetting a Range
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
|
|||
|
|||
Offsetting a Range
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" wrote: 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
|
|||
|
|||
Offsetting a Range
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offsetting | Excel Discussion (Misc queries) | |||
Offsetting and HLOOKUP | Excel Worksheet Functions | |||
Cont'd - Offsetting within a range dynamically - But returning a sum of all instances criteria are met | Excel Worksheet Functions | |||
Offsetting Cells | Excel Programming | |||
Offsetting a varible?? | Excel Programming |