+ Reply to Thread
Results 1 to 8 of 8

User Input Checking & Formula Looping

  1. #1
    Forum Contributor
    Join Date
    07-16-2009
    Location
    australia
    MS-Off Ver
    Excel 2007
    Posts
    116

    User Input Checking & Formula Looping

    I am having a little issue with some code.

    My Goal: Is to have the macro stop allow the user to enter input data into columns BI & BJ. The input will be simple either 0 or 1 or 2. Once input done I would hope it would then loop through formula and place results in BK & BL respectively.

    I have written some code but I haven't coded how to stop and start after user input. I have attempted to check for errors in input. Any feedback or suggestion appreciated.

    I have attached a sample sheet.

    This is my current code.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by flebber; 09-09-2010 at 11:24 AM.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: User Input Checking & Formula Looping

    Hi

    This is only to give you some ideas, but see if it helps
    Please Login or Register  to view this content.
    rylo

  3. #3
    Forum Contributor
    Join Date
    07-16-2009
    Location
    australia
    MS-Off Ver
    Excel 2007
    Posts
    116

    Re: User Input Checking & Formula Looping

    Okay that looks like it streamlines the process a bit. With regards to the user input.
    Please Login or Register  to view this content.
    Using the input box will the box error if for example I type a 4 say in instead of a 1?

  4. #4
    Forum Contributor
    Join Date
    07-16-2009
    Location
    australia
    MS-Off Ver
    Excel 2007
    Posts
    116

    Re: User Input Checking & Formula Looping

    The error I am having with it when testing it is that it returns the same value regardless of row the user value is entered into.

    So that if I am in row 5 and enter 1 it uses the values defined in row 5 so that
    Please Login or Register  to view this content.
    becomes

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: User Input Checking & Formula Looping

    Hi

    1) There is no checking on the input to the input box. You would have to restrict things. Maybe

    Please Login or Register  to view this content.
    2) I just grabbed one of your formulas that you had and pasted it in. Your example seemed to take the same formula regardless of the row position, so I just left it there.

    Couple of things you could do
    a) Convert to a RC format so you are not specifying the row
    b) Use the row from the variable ce (
    Please Login or Register  to view this content.
    ) and dynamically build the formula that way. Plenty of examples on the site of how this can be done.

    rylo

  6. #6
    Forum Contributor
    Join Date
    07-16-2009
    Location
    australia
    MS-Off Ver
    Excel 2007
    Posts
    116

    Re: User Input Checking & Formula Looping

    regarding

    b) Use the row from the variable ce () and dynamically build the formula that way. Plenty of examples on the site of how this can be done.
    Can I ask what search term to use. I am using "dynamic row variables" and "formula dynamic variables" and similar variations but get results about dynamic arrays and charts

    Edit
    I have found a tutorial on contextures regarding creating names and using them in a sumproduct formula. http://www.contextures.com/xlNames03.html#Macro

    might use r1c1.
    Please Login or Register  to view this content.
    Last edited by flebber; 09-09-2010 at 07:17 PM.

  7. #7
    Forum Contributor
    Join Date
    07-16-2009
    Location
    australia
    MS-Off Ver
    Excel 2007
    Posts
    116

    Re: User Input Checking & Formula Looping

    I am having trouble getting this to work. I tried to add an option to get to add the relative row of C & N to the inputbox to show me what person I was making the entry for. I also tried to add an option to allow user to exit.

    Can I get a little help I have sorted out issue with formula's but just can't get it to work.

    Please Login or Register  to view this content.
    I have attached a sample sheet
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: User Input Checking & Formula Looping

    Hi

    try something like

    Please Login or Register  to view this content.
    rylo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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