Home

How to select a cell relative to the active cell in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-293913%22%20slang%3D%22en-US%22%3EHow%20to%20select%20a%20cell%20relative%20to%20the%20active%20cell%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293913%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20writing%20a%20VBA%20program%20to%20add%20numbers%20to%20a%20cell%20on%20a%20separate%20sheet.%26nbsp%3B%20The%20targeted%20cell%20will%20change%20every%20time%20I%20use%20the%20program.%26nbsp%3B%20However%2C%20one%20way%20to%20identify%20the%20cell%20is%20to%20use%20Find%20to%20get%20to%20the%20proper%20row.%26nbsp%3B%20The%20targeted%20cell%20is%20two%20columns%20over.%26nbsp%3B%20How%20to%20I%20achieve%20this%20without%20too%20much%20trouble.%26nbsp%3B%20I%20have%20tried%20the%20Row%20function%20but%20have%20not%20been%20able%20to%20make%20it%20work%20in%20VBA.%26nbsp%3B%20Any%20suggestions%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-293913%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294136%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20select%20a%20cell%20relative%20to%20the%20active%20cell%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294136%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F226559%22%20target%3D%22_blank%22%3E%40Gis%C3%A8le%20et%20Yves%20Pinet%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20%22offset%22%20property%20to%20change%20which%20cell%20you%20want%20to%20select%20based%20on%20where%20your%20active%20cell%20is.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20say%20your%20active%20cell%20is%20in%20A13%20and%20you%20want%20to%20move%20it%20over%202%20columns%3B%20all%20you%20need%20to%20write%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActivecell.Offset(0%2C2).select%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20offset%20the%20active%20cell%26nbsp%3Bdown%200%20rows%20and%26nbsp%3Bto%20the%20right%202%20columns.%20If%20you%20ever%20want%20to%20go%20the%20other%20way%20just%20put%20(-)negative%20signs%20in%20front%20of%20the%20numbers.%20Hope%20this%20helps!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOffset(%23ofRows%2C%23ofColumns)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPositive%20numbers%3A%3C%2FP%3E%3CP%3ERows%20%3D%20down%3C%2FP%3E%3CP%3EColumns%20%3D%20to%20the%20right%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENegative%20numbers%3A%3C%2FP%3E%3CP%3ERows%20%3D%20up%3C%2FP%3E%3CP%3EColumns%20%3D%20to%20the%20left%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gisèle et Yves Pinet
Occasional Visitor

I am writing a VBA program to add numbers to a cell on a separate sheet.  The targeted cell will change every time I use the program.  However, one way to identify the cell is to use Find to get to the proper row.  The targeted cell is two columns over.  How to I achieve this without too much trouble.  I have tried the Row function but have not been able to make it work in VBA.  Any suggestions would be appreciated.

1 Reply

Hello @Gisèle et Yves Pinet,

 

You can use the "offset" property to change which cell you want to select based on where your active cell is. 

 

For example, say your active cell is in A13 and you want to move it over 2 columns; all you need to write is:

 

Activecell.Offset(0,2).select

 

This will offset the active cell down 0 rows and to the right 2 columns. If you ever want to go the other way just put (-)negative signs in front of the numbers. Hope this helps! 

 

Offset(#ofRows,#ofColumns)

 

Positive numbers:

Rows = down

Columns = to the right

 

Negative numbers:

Rows = up

Columns = to the left

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies