range offset
 

trying to set the active cell to the value of another cell via an offset
reference
have tried all these and mostly get an 1004 illegal object error
ActiveCell.Value = Sheets("schd").Cells(-2, 6).Value
ActiveCell.Value = workSheets("schd").Cells(-2, 6).Value
ActiveCell.Value = Range(Cells(-2,6), Cells(-2,6)).value

the formula works if i hard code the value like
ActiveCell.Value = Range("J2:J2).value

but need to have the offset methodology

there must be some way to do this with an 'offset' reference?? yes??

range offset
 

ActiveCell.Value = Sheets("schd").Offset(-2, 6).Value

The Cells property cannot take negative values as it refers to the rows and
columns relative to the sheet, and negative is invalid. Offset refers to the
activecell, so there can be a negative value here.

--
HTH

Bob Phillips

"bbxrider" wrote in message
...
trying to set the active cell to the value of another cell via an offset
reference
have tried all these and mostly get an 1004 illegal object error
ActiveCell.Value = Sheets("schd").Cells(-2, 6).Value
ActiveCell.Value = workSheets("schd").Cells(-2, 6).Value
ActiveCell.Value = Range(Cells(-2,6), Cells(-2,6)).value

the formula works if i hard code the value like
ActiveCell.Value = Range("J2:J2).value

but need to have the offset methodology

there must be some way to do this with an 'offset' reference?? yes??

range offset
 

Try this...


ActiveCell.Value = ActiveCell.Offset(-2, 6).Value


HTH,
Matt



"bbxrider" wrote:

trying to set the active cell to the value of another cell via an offset
reference
have tried all these and mostly get an 1004 illegal object error
ActiveCell.Value = Sheets("schd").Cells(-2, 6).Value
ActiveCell.Value = workSheets("schd").Cells(-2, 6).Value
ActiveCell.Value = Range(Cells(-2,6), Cells(-2,6)).value

the formula works if i hard code the value like
ActiveCell.Value = Range("J2:J2).value

but need to have the offset methodology

there must be some way to do this with an 'offset' reference?? yes??

range offset
 

I don't think you can have Cells(-2, 6)

You need a range and an offset. For example:

?Cells(4,4).offset(-2,6).address = $J$2
?Range("D4").offset(-2,6).address = $J$2

Regards

Trevor


"bbxrider" wrote in message
...
trying to set the active cell to the value of another cell via an offset
reference
have tried all these and mostly get an 1004 illegal object error
ActiveCell.Value = Sheets("schd").Cells(-2, 6).Value
ActiveCell.Value = workSheets("schd").Cells(-2, 6).Value
ActiveCell.Value = Range(Cells(-2,6), Cells(-2,6)).value

the formula works if i hard code the value like
ActiveCell.Value = Range("J2:J2).value

but need to have the offset methodology

there must be some way to do this with an 'offset' reference?? yes??

range offset
 

Are you looking for

ActiveCell.Value = ActiveCell.Offset(-2,6).Value

--
steveB

Remove "AYN" from email to respond
"bbxrider" wrote in message
...
trying to set the active cell to the value of another cell via an offset
reference
have tried all these and mostly get an 1004 illegal object error
ActiveCell.Value = Sheets("schd").Cells(-2, 6).Value
ActiveCell.Value = workSheets("schd").Cells(-2, 6).Value
ActiveCell.Value = Range(Cells(-2,6), Cells(-2,6)).value

the formula works if i hard code the value like
ActiveCell.Value = Range("J2:J2).value

but need to have the offset methodology

there must be some way to do this with an 'offset' reference?? yes??

range offset
 

Hi BBXRider

have tried all these and mostly get an 1004 illegal object error
ActiveCell.Value = Sheets("schd").Cells(-2, 6).Value
ActiveCell.Value = workSheets("schd").Cells(-2, 6).Value
ActiveCell.Value = Range(Cells(-2,6), Cells(-2,6)).value

Firstly, ActiveCell defines the sheet, so expressions like:

Sheets("schd").
or

Sheets("schd").

are unnecesary.

Secondly, the Offset function needs an anchor range. In your case, the
anchor cell might be the active cell.

If, for example, the active cell were D4 then

ActiveCell.Offset(-2,6)

would return Cell J2.

The ActiveCell.Offset(-2,6) expression is interpreted by Excel as defining a
cell which is 2 rows above and 6 rows to the right of the anchor cell (in
this instance, the active cell). If the first coodinate (-2) is negatve,
the target cell (the cell returned by the offset expression) is above the
anchor cell; if it is positive, the target cell is below the nchor cell.
Similarly, if the second coordinate(6) is positive, the target is to the
right of the anchor cell; if it is negative, the target is to the left of
the anchor cell.

There are no zero or negative rows or columns in a worksheet, so any offset
expression which returns a taget cell whose row or column cordinates are <
1, wll engender a 1004 error message.

Look at VBA's help on the Offset property.

---
Regards,
Norman



"bbxrider" wrote in message
...
trying to set the active cell to the value of another cell via an offset
reference
have tried all these and mostly get an 1004 illegal object error
ActiveCell.Value = Sheets("schd").Cells(-2, 6).Value
ActiveCell.Value = workSheets("schd").Cells(-2, 6).Value
ActiveCell.Value = Range(Cells(-2,6), Cells(-2,6)).value

the formula works if i hard code the value like
ActiveCell.Value = Range("J2:J2).value

but need to have the offset methodology

there must be some way to do this with an 'offset' reference?? yes??

range offset
 

If you qualify the Cells method with a Worksheet object, it will use the
first cell (A1) on that worksheet as the anchor. This means you can't use a
negative number as one of it's arguments because there are no negative row
numbers or negative column numbers on a worksheet.

If, on the other hand, you qualify the Cells method with a Range object, it
will use the first cell in that Range as the anchor, and you can offset
negatively from that anchor as far as you like (as long as it doesn't end up
beyond the edges of the worksheet).


Regards,
Vic Eldridge



"bbxrider" wrote:

trying to set the active cell to the value of another cell via an offset
reference
have tried all these and mostly get an 1004 illegal object error
ActiveCell.Value = Sheets("schd").Cells(-2, 6).Value
ActiveCell.Value = workSheets("schd").Cells(-2, 6).Value
ActiveCell.Value = Range(Cells(-2,6), Cells(-2,6)).value

the formula works if i hard code the value like
ActiveCell.Value = Range("J2:J2).value

but need to have the offset methodology

there must be some way to do this with an 'offset' reference?? yes??

range offset
 

Hi Vic,

If you qualify the Cells method with a Worksheet object, it will use the
first cell (A1) on that worksheet as the anchor. This means you can't use
a
negative number as one of it's arguments because there are no negative row
numbers or negative column numbers on a worksheet.
..
And, of course, if the Cells property is qualified with any object qualifier
other than a range object, or is used without explicit qualification, no
natural number index values can be used because they would implicitly expand
the worksheet beyond the restrictions of its 65536 x 256 boundary.

In short, the Cells property will not permit any index (other than 0)
unless it is qualified by a suitable range object.


---
Regards,
Norman


"Vic Eldridge" wrote in message
...
If you qualify the Cells method with a Worksheet object, it will use the
first cell (A1) on that worksheet as the anchor. This means you can't use
a
negative number as one of it's arguments because there are no negative row
numbers or negative column numbers on a worksheet.

If, on the other hand, you qualify the Cells method with a Range object,
it
will use the first cell in that Range as the anchor, and you can offset
negatively from that anchor as far as you like (as long as it doesn't end
up
beyond the edges of the worksheet).

---
Regards,
Norman