SOLVED
Home

VBA code to move fr. active cell to single cell X rows/Y columns away, selecting nothing in between?

%3CLINGO-SUB%20id%3D%22lingo-sub-730239%22%20slang%3D%22en-US%22%3EVBA%20code%20to%20move%20fr.%20active%20cell%20to%20single%20cell%20X%20rows%2FY%20columns%20away%2C%20selecting%20nothing%20in%20between%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730239%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20surprsingly%20tricky.%3C%2FP%3E%3CP%3ELet's%20say%20I%20have%20two%20named%20cells%3A%3CBR%20%2F%3E%22Move_this_many_rows%22%20--%20let's%20say%20for%20example%20it%20contains%20a%203%3CBR%20%2F%3E%22Move_this_many_columns%22%20--%20let's%20say%20for%20example%20it%20contains%20a%204%3C%2FP%3E%3CP%3ELet's%20also%20say%20that%20the%20active%20cell%20is%20A1.%3C%2FP%3E%3CP%3EWhen%20I%20run%20the%20following%20code%2C%20it%20expands%20the%20selection%2C%20from%20A1%3AE4.%20This%20part%20is%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20StartingCell%20As%20Range%2C%20EndingCell%20As%20Range%3CBR%20%2F%3ESet%20StartingCell%20%3D%20ActiveCell%3CBR%20%2F%3ESet%20EndingCell%20%3D%20Range(StartingCell%2C%20StartingCell.Offset(Range(%22Move_this_many_rows%22)%2C%20Range(%22Move_this_many_columns%22)))%3CBR%20%2F%3EEndingCell.Select%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20ends%20with%20A1%3AE4%20selected%2C%20whereas%20what%20I%20want%20is%20for%20only%20E4%20to%20be%20selected.%20And%20I%20can't%20figure%20out%20how%20to%20get%20from%20the%20state%20of%20slecting%20A1%3AE4%20to%20just%20selecting%20E4.%3C%2FP%3E%3CP%3E(By%20the%20way%2C%20the%20space%20I'm%20working%20in%20might%20have%20rows%2F%20columns%20around%20it%2C%20so%20none%20of%20these%20cells%20will%20be%20the%20last%20ones%20used%20on%20the%20sheet.)%3C%2FP%3E%3CP%3EFrustrating%2C%20as%20it's%20so%20simple%3C%2FP%3E%3CP%3E--%20to%20write%20the%20worksheet%20version%20of%20the%20OFFSET%20formula%20I%20want%20--%20if%20I%20want%20to%20move%203%20rows%20down%20and%204%20columns%20to%20the%20right%2C%203%20and%204%20are%20the%20OFFSET%20function's%202nd%20and%203rd%20arguments.%3C%2FP%3E%3CP%3E--%20manually%20with%20the%20macro%20recorder.%20I%20know%20I%20want%20to%20just%20move%203%20rows%20down%20and%204%20to%20the%20right%2C%20the%20code%20is%3CBR%20%2F%3EActiveCell.Offset(3%2C%204).Range(%22A1%22).Select%3C%2FP%3E%3CP%3EI've%20got%20two%20named%20cells%2C%20one%20containing%20the%203%20and%20the%20other%2C%20the%204%2C%20yet%20I%20can't%20seem%20to%20refer%20to%20them%20in%20this%20simple%20line%20of%20code.%3C%2FP%3E%3CP%3EAny%20advice%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-730239%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751469%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20move%20fr.%20active%20cell%20to%20single%20cell%20X%20rows%2FY%20columns%20away%2C%20selecting%20nothing%20in%20betw%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368976%22%20target%3D%22_blank%22%3E%40lingyai%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESub%20Offsetting()%0AActiveCell.Offset(Range(%22Move_this_many_rows%22).Value%2C%20Range(%22Move_this_many_columns%22).Value).Select%0AEnd%20Sub%3C%2FPRE%3E%0A%3CP%3EI%20rewrote%20your%20code%20as%20a%20one-liner%20to%20move%20the%20active%20cell.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20code%20was%20creating%20a%20range%20object%20spanning%20the%20cells%20between%20StartingCell%20and%20EndingCell.%20Actually%2C%20you%20just%20needed%20the%20part%20that%20begins%20StartingCell.Offset(...%3C%2FP%3E%0A%3CPRE%3ESet%20EndingCell%20%3D%20Range(StartingCell%2C%20StartingCell.Offset(Range(%22Move_this_many_rows%22)%2C%20Range(%22Move_this_many_columns%22)))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-751613%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20move%20fr.%20active%20cell%20to%20single%20cell%20X%20rows%2FY%20columns%20away%2C%20selecting%20nothing%20in%20betw%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374823%22%20target%3D%22_blank%22%3E%40Brad_Yundt%3C%2FA%3E%26nbsp%3B%20thanks%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
lingyai
New Contributor

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?

2 Replies
Solution

@lingyai 

Sub 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")))
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies