+ Reply to Thread
Results 1 to 6 of 6

Validation riddle with VBA

  1. #1
    Registered User
    Join Date
    12-29-2011
    Location
    San Diego, Ca.
    MS-Off Ver
    Excel 2007
    Posts
    60

    Validation riddle with VBA

    The intent here is simple. On sheet1, Column A1-A100, each cell is allowed a certain entry as determined by a list in B1-B6. Very simple.

    Here is the VBA code that works.

    Please Login or Register  to view this content.
    If I add the following variable
    secstr = "$B$6" and substitute the following for rlist:

    rlist=Chr(34) & "$B$1:" & secstr & Chr(34), it results in rlist being exactly "$B$1:$B$6" as before, but the procedure fails, the dreaded Run time error 1004 that hangs on the 2 lines starting with .ADD.

    Here is the modified code.

    Please Login or Register  to view this content.


    Any ideas?
    Last edited by Leith Ross; 02-02-2019 at 05:24 PM. Reason: Fixed Code Tags

  2. #2
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    199

    Re: Validation riddle with VBA

    Could you attach the workbook? Also, it would be good to put your code inside ['CODE] CODE HERE [/CODE] tags, easier to read.

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Validation riddle with VBA

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  4. #4
    Registered User
    Join Date
    12-29-2011
    Location
    San Diego, Ca.
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Validation riddle with VBA

    Edited the code so it is hopefully clear where the code is embedded.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Validation riddle with VBA

    In
    Please Login or Register  to view this content.
    The quote marks are not part of the string, they are there to tell the compiler where the string starts and stops.

    In
    Please Login or Register  to view this content.
    The Chr(34) (quote marks) are part of the string.

    That's why they act differently when used in list definition

    Run this test program to see the difference

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Registered User
    Join Date
    12-29-2011
    Location
    San Diego, Ca.
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Validation riddle with VBA

    Thanks. The light turned on when your comments about how the compiler works sank in. We need more lucid explanations like this that just don't provide the solution but provide how the interpreter or compiler 'understands' what we write or code.

    Great job!!

+ 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. riddle in excel
    By angelos1111 in forum Excel General
    Replies: 7
    Last Post: 01-24-2019, 04:11 PM
  2. update data validation sources if validation values change
    By mauler69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2016, 05:10 PM
  3. Replies: 3
    Last Post: 05-09-2016, 08:27 PM
  4. validation rules not working when someone copy paste data on validation cell
    By jthakrar in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2010, 03:36 AM
  5. Text wrap riddle!
    By nipakr in forum Excel General
    Replies: 2
    Last Post: 04-23-2008, 05:33 AM
  6. Excel formula riddle
    By egeorge4 in forum Excel General
    Replies: 6
    Last Post: 01-09-2006, 09:55 PM
  7. R1C1 riddle
    By Scriptick in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-07-2005, 03:05 PM

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