+ Reply to Thread
Results 1 to 11 of 11

(Beginner help) For loop inside do loop that displays information from reference sheet.

  1. #1
    Registered User
    Join Date
    06-07-2016
    Location
    Great White North
    MS-Off Ver
    Office 365
    Posts
    10

    (Beginner help) For loop inside do loop that displays information from reference sheet.

    Hi all. Beginner working with VBA, and pulling out my teeth. I need to compile some code that accomplishes a number of things:

    - Asks the user for a number, with Do loops that keep prompting until correct data is entered (must be numerical, must not be nothing, must be non-negative)
    - Use a For loop that goes from 1 to this number
    - Then asks for codes (listed in a reference sheet), must loop until a code listed in the sheet is entered, that corresponds to the number
    - Finally, must display total cost attached to sum of the codes

    I also need to use a variant variable for each input prior to error testing, and once error testing is done, I must assign it to the correct variable type.

    This is as far as I've gotten:

    Public Sub ProductsPurchased()
    productCount = InputBox("Please enter the number of different products purchased.")
    Do
    If IsNumeric(productCount) = False Then
    MsgBox "You have not entered a numerical value. Please try again."
    Else
    IsNumeric (productCount) < 0
    MsgBox "You have entered a number less than or equal to zero. Please try again."
    End If
    Loop Until IsNumeric(productCount) And productCount > 0
    End Sub

    I am almost entirely lost. Please, help.

  2. #2
    Forum Contributor
    Join Date
    08-10-2009
    Location
    nottingham
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    Not sure what you're needing?

    It does look like you need an elseIf though:

    "If" input is not a number, "Then" do this...

    "ElseIf" input is a number less than zero, "Then" do this

    "Else"

    Do this

    "EndIf"

  3. #3
    Registered User
    Join Date
    06-07-2016
    Location
    Great White North
    MS-Off Ver
    Office 365
    Posts
    10

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    Quote Originally Posted by inq80 View Post
    Not sure what you're needing?

    It does look like you need an elseIf though:

    "If" input is not a number, "Then" do this...

    "ElseIf" input is a number less than zero, "Then" do this

    "Else"

    Do this

    "EndIf"

    Thank you for responding. I tried Else Loop Until IsNumeric(productCount) AND productCount >0, but then even if the number is greater than zero, it still shows me the "less than 0" message box.

  4. #4
    Forum Contributor
    Join Date
    08-10-2009
    Location
    nottingham
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    Can you attach a sample sheet?

  5. #5
    Registered User
    Join Date
    06-07-2016
    Location
    Great White North
    MS-Off Ver
    Office 365
    Posts
    10

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    Quote Originally Posted by inq80 View Post
    Can you attach a sample sheet?
    Not sure what you mean. Attach the file I'm working in?

  6. #6
    Forum Contributor
    Join Date
    08-10-2009
    Location
    nottingham
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    yeah - attach the worksheet so everyone can see what you're working with.

  7. #7
    Registered User
    Join Date
    06-07-2016
    Location
    Great White North
    MS-Off Ver
    Office 365
    Posts
    10

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    Quote Originally Posted by inq80 View Post
    yeah - attach the worksheet so everyone can see what you're working with.
    Sorry I didn't see this earlier. I can't. All I get is a thin white bar when I try to attach a document directly. Best I can do is Dropbox.

    https://www.dropbox.com/s/1gzod757zw...Data.xlsm?dl=0

  8. #8
    Forum Contributor
    Join Date
    08-10-2009
    Location
    nottingham
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    I think you're setting yourself up for problems using an input box. Dependent on where you are going with this, try this:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-07-2016
    Location
    Great White North
    MS-Off Ver
    Office 365
    Posts
    10

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    Quote Originally Posted by inq80 View Post
    I think you're setting yourself up for problems using an input box. Dependent on where you are going with this, try this:
    Thank you so much. You gave me an idea, I tried it, and it worked!

    Please Login or Register  to view this content.
    I set the variables, selected the ranges, ran the Do loops inside a For loop where myNum is equal to an index from 1 to that number, set the message boxes to provide the user with feedback, and got the totals on the other end. You've been a huge help.

  10. #10
    Forum Contributor
    Join Date
    08-10-2009
    Location
    nottingham
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    You're Welcome: don't forget to make the thread as resolved if you've got an answer - and considering adding to the reputation of whoever helped you.

  11. #11
    Registered User
    Join Date
    06-07-2016
    Location
    Great White North
    MS-Off Ver
    Office 365
    Posts
    10

    Re: (Beginner help) For loop inside do loop that displays information from reference sheet

    Quote Originally Posted by inq80 View Post
    You're Welcome: don't forget to make the thread as resolved if you've got an answer - and considering adding to the reputation of whoever helped you.
    Thank you. Already threw you some rep. Will mark as resolved now.

+ 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. Can I apply a loop inside a loop?
    By vibhor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-18-2015, 11:55 AM
  2. [SOLVED] How to restrict pop of message box inside multiple times when it is inside a for loop?
    By timmu in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2014, 06:00 AM
  3. [SOLVED] For loop inside for loop starts over.
    By lavinius in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2013, 08:57 AM
  4. [SOLVED] For Next Loop with IF statement inside does not loop
    By roxybethany in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-09-2013, 11:45 AM
  5. Changing the index of a for loop inside of the loop
    By drinkmorewine in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-03-2013, 12:19 PM
  6. [SOLVED] Loop inside a loop problem!
    By questionguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2012, 12:54 PM
  7. VBA For loop where number of iterations can be changed from inside loop
    By barrboy89 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-11-2011, 12:18 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