Doing a loop in excel

cpass82 · #1 (**permalink**) 06-01-04, 21:11

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.

MikeTheBike · #2 (**permalink**) 06-02-04, 07:46

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

MikeTheBike · #3 (**permalink**) 06-02-04, 07:48

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

DavidCoutts · #4 (**permalink**) 06-02-04, 07:58

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")*(D2

23=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

cpass82 · #5 (**permalink**) 06-02-04, 11:35

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.

JSThePatriot · #6 (**permalink**) 06-02-04, 12:41

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

cpass82 · #7 (**permalink**) 06-02-04, 13:15

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.

MikeTheBike · #8 (**permalink**) 06-03-04, 08:32

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

cpass82 · #9 (**permalink**) 06-03-04, 11:44

Thank you for your help.

Internet Services

Web Development

Digital Marketing

Consumer Tech