+ Reply to Thread
Results 1 to 5 of 5

A Way to Refer to Cells So I Can Move Them Around?

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    25

    A Way to Refer to Cells So I Can Move Them Around?

    Hi VBA wizards,

    I have what is probably an Excel/VBA 101 problem. But I don’t know what terms to Google to determine the solution.

    I have a workbook where the first tab (“Sheet1”) is a form for users to fill out:


    When you click the button, a Macro runs and useable data is generated on another tab (“Sheet2”):

    Please Login or Register  to view this content.

    Pretty simple stuff.

    Here’s the problem: Suppose after building the user interface on Sheet1, I realize that I have to redesign it, move some fields around, add a feature, etc:


    The problem here is now the Macro’s hardcoded references to cells are now incorrect. I need to manually reread through every line of my code and make corrections. That’s not a huge problem with this toy example, but a massive pain when my actual spreadsheet at work has over 1,000 lines of VBA code and twenty tabs. (Really!)

    There’s got to be a way to dynamically reference the user-edited fields in Sheet1 so that I can move them around without having to re-edit the VBA all the time. I noticed the “Define Name” option when I right-click on a cell. A “name” would seem to be what I need, but when I Google “excel vba cell name variable” or some subset of those words, I pull up a lot of information on named ranged or named variables, but nothing on cells.

    Is there some way to do this? If so, what terminology should I search on? Thanks

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: A Way to Refer to Cells So I Can Move Them Around?

    Look for Named Range

    https://support.office.com/en-gb/art...2-abd7ff379c64
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: A Way to Refer to Cells So I Can Move Them Around?

    You can use Range("name") just as you use Range("C3") in a macro.
    Have a look here for some MS examples: https://docs.microsoft.com/en-gb/off...o-named-ranges

    You will need to define each Name first, either by using the Name box to the left of the formula box or using 'Name Manager' on the 'Formula' tab. This will be a bit time-consuming, but if you have a lot of changes and a lot of potential cell references in macros, it's probably worth it. Using Names also makes both formulae and macros easier to read (no need to remember that C3 is the number of eggs chosen if you use 'Chosen_Nr_of_Eggs' instead, for example).
    Have a look here (and on the linked pages) for more info: https://support.office.com/en-gb/art...4-9f61bd5c64e4

    If you want to find more, try searching for 'excel vba reference to named range' or something similar.

    Hope that helps.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: A Way to Refer to Cells So I Can Move Them Around?

    Thanks for the rep.

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: A Way to Refer to Cells So I Can Move Them Around?

    Thanks for the rep. If that means that you're happy that your question has been answered, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.

+ 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. [SOLVED] Refer to other cells
    By damianclarkson in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-09-2018, 03:24 AM
  2. Using Range to refer to cells
    By malcmail in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-15-2018, 01:05 PM
  3. Refer only cells with content
    By magic2finger in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-08-2016, 09:36 AM
  4. Move and size with cells greyed out- form control checkboxes move after printing 2010
    By Duffy1974 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-26-2012, 09:19 AM
  5. Refer to cells in the range consisting of non-adjacent cells
    By dmitry in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2011, 10:48 AM
  6. Refer to cells in array
    By krabople in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2007, 03:53 AM
  7. [SOLVED] refer to a cells value in a macro
    By loulou in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-24-2005, 10:06 AM

Tags for this Thread

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