VBA Code: Reference cell based on offset of selected cell from Application.InputBox

**Cm214** · 09-30-2021, 12:25 AM

Evening guys,

I am trying to figure out the correct syntax to get Excel to record a cell reference based on an specified offset of a cell selected using the Application.InputBox function.

In example, say I select a cell D10 in a spreadsheet for "VolumeSelect" and it is stored as a Variant/String type as ('Sheet'!$D$10). I want VolumeOffset1 to be the cell reference in the same spreadsheet with a specified offset of that selected cell, say one row down and one column to the right ('Sheet1'!$E$11)

I provided what I have so far. When running the code, I get Run-time error 424 (Object required)

Any help would greatly be appreciated!

**Artik** · 09-30-2021, 01:43 AM

In the InputBox window, you can see the address of the selected cell in A1 notation, but the variable receives a formula in R1C1 notation. As you mentioned, the VolumeSelect variable holds a Variant / String type, so it is not an object. You somehow need to turn the text into an object.
For example like this:

Please Login or Register  to view this content.

Artik

**Cm214** · 09-30-2021, 10:54 AM

That seemed to work in converting it into an object. Now the only issue I have is that the cell address is in absolute reference. When executing, the VolumeOffset1 still returns a value of "".

Is there a way I can turn VolumeSelect from a absolute reference state to a relative reference state?

**Artik** · 10-01-2021, 09:14 AM

Probably the macro might look like this:

Please Login or Register  to view this content.

Artik

VBA Code: Reference cell based on offset of selected cell from Application.InputBox

LinkBack

Thread Tools

Rate This Thread

Display

VBA Code: Reference cell based on offset of selected cell from Application.InputBox

Re: VBA Code: Reference cell based on offset of selected cell from Application.InputBox

Re: VBA Code: Reference cell based on offset of selected cell from Application.InputBox

Re: VBA Code: Reference cell based on offset of selected cell from Application.InputBox

Thread Information

Users Browsing this Thread

Similar Threads

Fixed the VBA code code to pick up the email address from the selected radio cell

want the vba code to pick up the email address from the selected radio cell

[SOLVED] Return Cell Address for Active/Selected Cell

[SOLVED] I Want to know how I can make this formula into vba code for dynamic cell address

Email selected print section to address located in a cell instead of email address in code

Excel 2007 : Formula to grab ip address from within text.

[SOLVED] how do I insert the address of a selected cell into a fixed cell

Bookmarks

Bookmarks

Posting Permissions