Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Question Unanswered: Doing a loop in excel

    I need to do a "do while" loop that you would do in visual basic...but in excel. I have four conditions that need to be met to give me a numerical vaule, such as:

    Column A Column B Column C Column D Column E
    40x28 Y F 1 48.23
    40x28 N P 2 53.25
    48x28 Y P 2 103.5

    How do I use the "IF" function to do this? Or is there a better function? the user can input the information for Columns A - D in a different spread sheet. Please help me.
    Last edited by cpass82; 06-01-04 at 22:17.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Do Loop in Excel

    Hi cpass82

    I do not have a clue what you are trying to do (more info would be helpfull) but a Do Loop in Excel VBA is the same as in VB6

    for example
    Code:
    Sub DoLoop()
        Dim Col1 As String
        Dim Col2 As String
        Dim Col3 As String
        Dim Col4 As String
        Dim iRow As Long
        
        iRow = 1
        Do While Cells(iRow, 1) <> ""
            Col1 = cels(iRow, 1)
            Col2 = cels(iRow, 2)
            Col3 = cels(iRow, 3)
            Col4 = cels(iRow, 4)
            
            'IF SOME CONDITION OF COL1 THROUGH COL4 THEN Cells(iRow,5)= "?"
            
            iRow = iRow + 1
        Loop
    End Sub
    This code reads the values of the 4 columns into string variables which can be manipulated how you like with VB to get a result to put in column 5 (ie column E), and stops when coulmn A is empty.

    Hope this is what you had in mind.

    MTB

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Corected Code !! Sorry
    Code:
    Sub DoLoop()
        Dim Col1 As String
        Dim Col2 As String
        Dim Col3 As String
        Dim Col4 As String
        Dim iRow As Long
        
        iRow = 1
        Do While Cells(iRow, 1) <> ""
            Col1 = cells(iRow, 1)
            Col2 = cells(iRow, 2)
            Col3 = cells(iRow, 3)
            Col4 = cells(iRow, 4)
            
            'IF SOME CONDITION OF COL1 THROUGH COL4 THEN Cells(iRow,5)= "?"
            
            iRow = iRow + 1
        Loop
    End Sub
    MTB

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Im not sure exactly what you mean. but ill have a go at answering your query anyway.

    To get a single answer on a row you could use the function

    =SUMPRODUCT((A2="40x28")*(B2="Y")*(C2="P")*(D2=1)* (E2))

    to get totals for certain conditions you could use this

    =SUMPRODUCT((A2:A23="40x28")*(B2:B23="Y")*(C2:C23= "P")*(D223=1)*(E2:E23))

    replacing 23 by your last row

    or you could even write your Do While loop in VBA

    for future posts can you try to be more specific about what you are trying to do.

    HTH

    David

  5. #5
    Join Date
    Jun 2004
    Posts
    4
    Sorry, I should have been more specific. I am trying get have the computer give me the values in Column E by the user putting in criteria that matches Column A-D.
    For instance: If the user puts in that they have a 40x28,N,P,2 then they should get 53.25.

    How do I link IF statements to return the values in Column E based on the criteria enter by the user? Should I use IF statements or is there a better way?
    Thank you for your help.
    Last edited by cpass82; 06-02-04 at 12:44.

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Okay you will have to give the function that you used to get that 53.25 so we can see how you are needing this done.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Jun 2004
    Posts
    4
    53.25 is a set vaule when the user has: 40x28,N,P,2. If the user had 40x28, Y, P, 2 then it would have a different value. 53.25 is not calculated it is a constant.

    Basically this is a program where the user puts in the four criterias listed above and I have all the possible matches listed in a different worksheet. I need the program to take the information the user puts in and find the match on the other worksheet.
    Thank you so much for looking at this.

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Doing a loopin Excel

    Hi cpass 82

    My suggestion is to include a hidded sheet in the workbook requiring this function, that link to the cells in the workbook sheet containing the lookup combinations.

    You can then write a lookup function to referance the four criteria in the sheet and loop through the data you have linked to, and then return the correct value (or not if no match).

    I have attached an example of what I mean, but wthout linking to the other sheet, but I guess you can sort that. The dummy (hidden) linked sheet is call data.

    As it stands in my example it ignores leading and trailing spaces but is case sensitive (use the UCase or LCASE function to make it none case sensitive).

    There are obviuosly many ways to write the code but it's a start !

    Hope this makes sense.

    MTB
    Attached Files Attached Files

  9. #9
    Join Date
    Jun 2004
    Posts
    4
    Thank you for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •