+ Reply to Thread
Results 1 to 13 of 13

Convert string to Range

  1. #1
    Registered User
    Join Date
    01-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    2016
    Posts
    14

    Convert string to Range

    Working Solution: https://www.excelforum.com/excel-pro...ml#post5265986


    I have a large range of cells I need to define:


    Please Login or Register  to view this content.
    and then set the ranges (Witch are basically the same:

    Please Login or Register  to view this content.

    I was trying to simplify everything by doing the following (So the above code would not be needed)

    I am using this code to grab the current cell column letter
    Please Login or Register  to view this content.
    and I was trying to use some code like this to create the Range based on the column letter
    Please Login or Register  to view this content.
    but how can I change that into something I can then use for this:

    Please Login or Register  to view this content.
    Last edited by almulder; 01-24-2020 at 12:05 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Convert string to Range

    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.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Convert string to Range

    You can use
    Please Login or Register  to view this content.
    to expand the range...

    Syntax is rangeobject.Resize(rowsize,columnsize) - to see what I mean, copy this into the editor and run it through using F8:

    Please Login or Register  to view this content.
    HTH
    Tim
    Never stop learning!
    <--- please consider *-ing !

  4. #4
    Registered User
    Join Date
    01-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    2016
    Posts
    14

    Re: Convert string to Range

    That does not look like what I am needing. I am trying to grab the active column letter and using that to create the range and then using that to call a sub.

    Would like to do this with out having to create many lines of code.

    Trying to replace this:
    Please Login or Register  to view this content.
    Also by having the above code, it runs through all the modules instead of just the one that it should be


    to something simple like (Which I know does not work. Hoping someone can point me in the right direction)

    Please Login or Register  to view this content.
    Last edited by almulder; 01-23-2020 at 08:49 PM.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Convert string to Range

    What do the CheckRow procedures do?
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    01-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    2016
    Posts
    14

    Re: Convert string to Range

    Mostly lock down certain cells and unlock others and some populate data into cells, just depends on what gets entered in each column.

    My goal is if they enter in a value into D# then on the same row unlock some cells, clear some cells and lock some cells all in the same row, then if they enter anything in E# same thing just different cells, and so one for the row, then when they start the next row, it would start again but the row number would be different.

    And I want the D_CheckRow to only run when D has been changed, then E_CheckRow to only run when E changed. ect..

    Just cant seem to figure out the code to make that happen, and make it simple so there are not lots of lines of code. (Either way I cant seem to make it work)

  7. #7
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Convert string to Range

    I think the issue is you're code was constructing a string, but you were trying to use it as a range.

    (BTW, I never use "ActiveCell", but maybe that's me just being paranoid. Does it not work with "Target.address" instead?)

    Anyway, does this do it for you?

    Please Login or Register  to view this content.
    If not, please attach a workbook with a sample of data that illustrates what you want to achieve.

    On a different note, would it not be more efficient to avoid having different functions for each range, but instead have a generic functions that accept a range as an argument and lock/unlock/clear etc. A list of the cells to pass for each could be kept separately in named ranges on a "configuration" worksheet and passed to the respective functions, and it would be easier to maintain.

    E.g.
    Please Login or Register  to view this content.
    Tim

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Convert string to Range

    Hello almulder,

    Using the Worksheet_Change event will greatly simplify your code.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  9. #9
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Convert string to Range

    Quote Originally Posted by Leith Ross View Post
    Please Login or Register  to view this content.
    Duh! So simple - why didn't I think of that!

  10. #10
    Registered User
    Join Date
    01-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    2016
    Posts
    14

    Re: Convert string to Range

    I have tried the code, right now I have only D and E created so I commented out the other column checks. but it never seem to run the D_LockUnlock the E_LockUnlock Modules.Not sure why they don't run. For now I have just a simple MsgBox that should come up when running the sub module (For testing) but it never pops up.

    I have uploaded my sample file I am messing with before I move it into my real project.

    Basically in D column on row 6 if you enter in 1,3, or 4 and press enter is should show the MsgBox D_LockUnlock, and if you enter in anything into E and press enter it should show MsgBox E_LockUnlock

    but that never happens.

    Much appreciate the help.
    Attached Files Attached Files

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Convert string to Range

    Hello almulder,

    Sorry, that was my fault. I thought I had changed the values to match your range. The values posted are for my test columns.

    Here is the corrected code...
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    01-21-2020
    Location
    Las Vegas, NV
    MS-Off Ver
    2016
    Posts
    14

    Re: Convert string to Range

    LOL I can believe I missed that part myself, I was even looking at other code places and was like ya column 1 is my first column im entering data on . LOL not it was 4 .

    This works just like I needed. Not to see now much I can muck it up into my real project . LOL

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Convert string to Range

    Hello almulder,

    I am glad you got a laugh out of my mistake and that is working the way you want.

+ 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] Convert Range to String
    By erice in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2018, 03:02 PM
  2. [SOLVED] Convert string to range
    By maw230 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-18-2015, 02:44 PM
  3. Convert string to range
    By ZacharyBass in forum Excel General
    Replies: 2
    Last Post: 11-26-2012, 12:58 AM
  4. Convert String to Row Range
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-03-2011, 04:07 PM
  5. Convert string to range
    By mavve2004 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2010, 07:07 PM
  6. Convert string to range
    By Cumberland in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-26-2006, 06:32 AM
  7. [SOLVED] convert a string to range?
    By JK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-19-2006, 08:10 PM

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