Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET Range | Excel Worksheet Functions | |||
Using Offset to name a range | Excel Worksheet Functions | |||
Offset Range | Excel Programming | |||
Offset and Range Selection | Excel Programming | |||
range offset | Excel Programming |