+ Reply to Thread
Results 1 to 14 of 14

Application.InputBox : how to capture the cell address into a variable and then re-use it.

  1. #1
    Registered User
    Join Date
    08-13-2019
    Location
    johannesburg, South Africa
    MS-Off Ver
    Office365-2016
    Posts
    8

    Application.InputBox : how to capture the cell address into a variable and then re-use it.

    The following Sub Procedure does not allow me to save the address of the selected cell into my variable "Rng" and then re-use it to
    select the cell location where i would like to copy the range selected via the second Application.Inputbox

    Please Login or Register  to view this content.
    Last edited by alansidman; 09-18-2019 at 09:14 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,394

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  3. #3
    Registered User
    Join Date
    08-13-2019
    Location
    johannesburg, South Africa
    MS-Off Ver
    Office365-2016
    Posts
    8

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Dear Alan
    Thanks for your reply, but I cannot where have u added the code for me, neither your reply.
    I am pretty new in this forum and although i have read you rules i sincerely do not understand them. They are overpowering ( there are some many !).
    Anyway , many thanks for your attempt at giving me a solution to my problem but i may have to pass from using this Forum in the future as I am unable to understand the coding that you require
    If that is OK with you then, I will post the question in another forum?
    Kindly let me know
    Thanking you in advance
    Regards
    Martin Argimon

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,802

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Rng is a Range object, and Address returns a String, so naturally you can't store one in the other. I can't really see the point of Rng when you already have OrderNo.
    Rory
    I drink, and I know things

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Not the coding But using # button to add coding tag around your code. So it will be nicely formatted into it's own box and easier to read.

    At any rate, your issue is in.
    Please Login or Register  to view this content.
    Rng is range object but Range.Address is string. So you are trying to set string into range object. Which isn't possible.

    Since you already have OrderNo as range object... I'm not sure what you are trying to do here.

    May be...
    Please Login or Register  to view this content.
    But .Address is same for entire MapSheet range.
    If this isn't what you wanted...
    It would be easier for us to help you if you can upload sample workbook demonstrating what you are trying to do with this code.

    To upload a sample workbook, use "Go Advanced" Button, follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.
    Last edited by CK76; 09-18-2019 at 10:21 AM. Reason: Added part about upload
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  6. #6
    Registered User
    Join Date
    08-13-2019
    Location
    johannesburg, South Africa
    MS-Off Ver
    Office365-2016
    Posts
    8

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Morning.
    I have been battling with the code below.
    Whenever i pick the value of a cell address in my sheet "Orders" (via the Application.Inputbox method), i will also like to store that very same cell "address" for future use as detailed under "Stage 2" ( refer to the code below).
    This program is basically designed to track "plot sales" ( land parcels). So the idea is to record the cell address of the plot ( which is the same as the cell address in the "Site Plan" sheet). Is sort of a geographically interface idea where each cell in the "SitePlan"sheet relates to a specific Land parcel, and hence once i select the cells (land parcels) in the "SitePlan" I want to record them back in the "Orders" sheet where I picked the Order number from initially.
    Hope my explanation is OK?
    Kindly assist. Thanks


    Private Sub CmdbuttAssignParcel_Click()

    Dim OrderNo As Range
    Dim MapSheet As Range
    Dim Rng As Range

    '***** Copies the value from the selected cell in sheet "Orders" and pastes the contents in sheet "SitePlan" ****
    '***** It also stores the cell address (where the value was first picked from), in the variable Rng for future use_
    '***** under Stage 2 ( see below) ****

    Sheets("Order").Activate
    Set OrderNo = Application.InputBox(Prompt:="Choose Order Number to Assign to a Parcel", Type:=8)
    Set Rng = OrderNo.Address

    Sheets("SitePlan").Activate
    Set MapSheet = Application.InputBox(Prompt:="Select Parcel/s on the Site", Type:=8)

    MapSheet.Value = OrderNo.Value

    '**** Stage 2 ****
    '**** Selects the cell in Sheet "Orders" where the "MapSheet.Address" will be copied to ( by using "Rng" and_
    '**** offest for the location) ******

    Sheets("Orders").Select
    Rng.Offset(0, 4).Select
    ActiveCell.Value = MapSheet.Address

    End Sub

  7. #7
    Registered User
    Join Date
    08-13-2019
    Location
    johannesburg, South Africa
    MS-Off Ver
    Office365-2016
    Posts
    8

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Morning.
    I have been battling with the code below.
    Whenever i pick the value of a cell address in my sheet "Orders" (via the Application.Inputbox method), i will also like to store that very same cell "address" for future use as detailed under "Stage 2" ( refer to the code below).
    This program is basically designed to track "plot sales" ( land parcels). So the idea is to record the cell address of the plot ( which is the same as the cell address in the "Site Plan" sheet). Is sort of a geographically interface idea where each cell in the "SitePlan"sheet relates to a specific Land parcel, and hence once i select the cells (land parcels) in the "SitePlan" I want to record them back in the "Orders" sheet where I picked the Order number from initially.
    Hope my explanation is OK?
    Kindly assist. Thanks


    Private Sub CmdbuttAssignParcel_Click()

    Dim OrderNo As Range
    Dim MapSheet As Range
    Dim Rng As Range

    '***** Copies the value from the selected cell in sheet "Orders" and pastes the contents in sheet "SitePlan" ****
    '***** It also stores the cell address (where the value was first picked from), in the variable Rng for future use_
    '***** under Stage 2 ( see below) ****

    Sheets("Order").Activate
    Set OrderNo = Application.InputBox(Prompt:="Choose Order Number to Assign to a Parcel", Type:=8)
    Set Rng = OrderNo.Address

    Sheets("SitePlan").Activate
    Set MapSheet = Application.InputBox(Prompt:="Select Parcel/s on the Site", Type:=8)

    MapSheet.Value = OrderNo.Value

    '**** Stage 2 ****
    '**** Selects the cell in Sheet "Orders" where the "MapSheet.Address" will be copied to ( by using "Rng" and_
    '**** offest for the location) ******

    Sheets("Orders").Select
    Rng.Offset(0, 4).Select
    ActiveCell.Value = MapSheet.Address

    End Sub

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,802

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    You could just replace:

    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-13-2019
    Location
    johannesburg, South Africa
    MS-Off Ver
    Office365-2016
    Posts
    8

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Hi rorya

    I had a Error message . See PDF attached
    Many Thanks
    Attached Files Attached Files

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,802

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Remove that line completely.

  11. #11
    Registered User
    Join Date
    08-13-2019
    Location
    johannesburg, South Africa
    MS-Off Ver
    Office365-2016
    Posts
    8

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    Sorry Rorya
    I can see that you really do not like me.
    You could track the answers that you gave me the first time i joined the forum ( some weeks ago) , which did not work either.
    Hence I think is better for me to stay away from this forum, since you are not replay helping me
    Thanks for trying
    Regards
    Martin Argimon

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,802

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    You've made 7 posts as far as I can see, and this is the first time I've replied to you, so I'm afraid I have no idea what you are talking about.

  13. #13
    Registered User
    Join Date
    08-13-2019
    Location
    johannesburg, South Africa
    MS-Off Ver
    Office365-2016
    Posts
    8

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    OK RORYA
    I was referring to some posts a few weeks ago. But please Not to worry
    I did not mean to be rude at all.
    Is just that the code did not work again and i got somehow 'desperate".

    I have tried your latest suggestion though ,as per your advised, ( to delete the 'error' line completely ) and now it works !!
    Please accept my deepest apologies, and many thanks for your assistance
    Once again sorry
    Kind Regards
    Martin Argimon

  14. #14
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,394

    Re: Application.InputBox : how to capture the cell address into a variable and then re-use

    @martinargimon

    You have received instructions on how to wrap your code in code tags. Please do so now.

    No further help to be offered this OP until Code tags are added to Posts 6 and 7.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Activate Cell after i click the value in Application.InputBox
    By AdiK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2016, 11:25 AM
  2. [SOLVED] Set range address as variable and then clear the cell via the variable
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2016, 02:23 PM
  3. Replies: 1
    Last Post: 10-14-2014, 07:17 AM
  4. [SOLVED] Assigning A Cell Address to A Variable, Using A Variable - Error '1003'
    By ch_abs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-28-2014, 01:36 PM
  5. Capture variable from part of cell formula
    By tbyrd1084 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2011, 05:27 PM
  6. Screen positioning inputbox that is set as Application.InputBox
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-06-2010, 07:59 AM
  7. [SOLVED] InputBox to capture user selected sheet names?
    By quartz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-03-2005, 06:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1