Jun 30 2019 10:20 AM
This is surprsingly tricky.
Let's say I have two named cells:
"Move_this_many_rows" -- let's say for example it contains a 3
"Move_this_many_columns" -- let's say for example it contains a 4
Let's also say that the active cell is A1.
When I run the following code, it expands the selection, from A1:E4. This part is fine.
Dim StartingCell As Range, EndingCell As Range
Set StartingCell = ActiveCell
Set EndingCell = Range(StartingCell, StartingCell.Offset(Range("Move_this_many_rows"), Range("Move_this_many_columns")))
EndingCell.Select
But it ends with A1:E4 selected, whereas what I want is for only E4 to be selected. And I can't figure out how to get from the state of slecting A1:E4 to just selecting E4.
(By the way, the space I'm working in might have rows/ columns around it, so none of these cells will be the last ones used on the sheet.)
Frustrating, as it's so simple
-- to write the worksheet version of the OFFSET formula I want -- if I want to move 3 rows down and 4 columns to the right, 3 and 4 are the OFFSET function's 2nd and 3rd arguments.
-- manually with the macro recorder. I know I want to just move 3 rows down and 4 to the right, the code is
ActiveCell.Offset(3, 4).Range("A1").Select
I've got two named cells, one containing the 3 and the other, the 4, yet I can't seem to refer to them in this simple line of code.
Any advice?
Jul 11 2019 08:24 PM
SolutionSub Offsetting() ActiveCell.Offset(Range("Move_this_many_rows").Value, Range("Move_this_many_columns").Value).Select End Sub
I rewrote your code as a one-liner to move the active cell.
Your code was creating a range object spanning the cells between StartingCell and EndingCell. Actually, you just needed the part that begins StartingCell.Offset(...
Set EndingCell = Range(StartingCell, StartingCell.Offset(Range("Move_this_many_rows"), Range("Move_this_many_columns")))
Jul 11 2019 08:24 PM
SolutionSub Offsetting() ActiveCell.Offset(Range("Move_this_many_rows").Value, Range("Move_this_many_columns").Value).Select End Sub
I rewrote your code as a one-liner to move the active cell.
Your code was creating a range object spanning the cells between StartingCell and EndingCell. Actually, you just needed the part that begins StartingCell.Offset(...
Set EndingCell = Range(StartingCell, StartingCell.Offset(Range("Move_this_many_rows"), Range("Move_this_many_columns")))