Help with Loop Function

I have an excel sheet with a couple of loop functions I'm working on. The first loop works. The second loop appears that it will work, however, the loop runs continuously. I think it's a truncation problem, but I just can't seem to solve it. The working loop is on Sheet1 (Single Batch Mode). The second loop is on sheet 11 (split batch mode). If anyone could take a look at the sheet and offer some advice on how to fix this, I would greatly appreciate it. Here's a link to the sheet. The details are outlined below.

http://dl.dropbox.com/u/80752316/BBL...p%20Buggy).xls

Once I get the loop for sheet11 working, my ultimate goal is to combine the two loops. Again, the loop for sheet 1 works just fine. Here are the details of what I want to do.

IF Sheet3 cell "$G$11" = "Fixed", then do until sheet11 cells "Z35" and "Z37" equal each other. If Sheet3 cell "$G$11" = "Equal", then do until sheet 11 cell "Z38" = sheet3 cell "F12".

Thanks in Advance,

Clue_Less

Here are some suggestions.
Use doubles rather than singles.

Do While (Sheet11.Range("$Z$37") <> iSparge)

When you compare two floating point numbers, it is best to Round each of them to x number of places. Perhaps something like:

Do While (Round(Sheet11.Range("$Z$37"), 2) <> Round(iSparge, 2))

The maximum number of significant digits in a double is around 15.

Originally Posted by StevenM

Here are some suggestions.
Use doubles rather than singles.

Do While (Sheet11.Range("$Z$37") <> iSparge)

When you compare two floating point numbers, it is best to Round each of them to x number of places. Perhaps something like:

Do While (Round(Sheet11.Range("$Z$37"), 2) <> Round(iSparge, 2))

The maximum number of significant digits in a double is around 15.

Thanks for the tips. I got the first loop to work by inserting a round function into the cells instead of the code. I tried the same thing with this one and it doesn't seem to work. I have no idea what I'm doing in VBA. I got the code from someone else and have been playing with it trying to get it to work.

I entered the code you gave me. It brought back an error. Compile Error: Sub or function not defined. And it highlighed "Round" in blue. I am using a pretty old version of excel from office 97 if that makes a difference.

Originally Posted by StevenM

Here are some suggestions.
Use doubles rather than singles.

Do While (Sheet11.Range("$Z$37") <> iSparge)

What do you mean by doubles? I changed the code and it did some wierd stuff. The two numbers used to hover very close to each other while the loop kept running, now they get pretty far apart and then start hovering. Here's what I changed.
Dim iLauter As Double
Dim iSparge As Double
Dim iElse As Double

Awesome! I'll try that out and see how it works. I appreciate your patience with explaining this all to me. This has definitely been a learning experience.

Two small corrections:

dim statements should come first.

Then: Application.EnableEvents = False

As below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dbLauter As Double
    Dim dbSparge As Double
    Dim dbElse As Double
    Dim db As Double

    Application.EnableEvents = False
    
    If Sheet3.Range("$G$11").Value = "Fixed" Then
        dbLauter = Format(Sheet11.Range("$Z$37").Value, "#.00")
        dbSparge = Format(Sheet11.Range("$Z$35").Value, "#.00")
        dbElse = Format(Sheet11.Range("$Z$41").Value, "#.000")
        db = Sheet11.Range("$Z$37")
        Do While (Round(db, 3) <> Round(dbSparge, 3))
            If Sheet11.Range("$Z$37") > dbSparge Then
                Sheet11.Range("$FO$503") = Sheet11.Range("$FO$503") - 0.001
            End If
            If Sheet11.Range("$Z$37") < dbSparge Then
                Sheet11.Range("$FO$503") = Sheet11.Range("$FO$503") + 0.001
            End If
            'Sheet11.Range("$FO$503") = Format(dbElse, "#.000")
            db = Sheet11.Range("$Z$37")
        Loop
    Else
        Sheet11.Range("$FO$503") = CDbl(Sheet11.Range("$Z$41").Value)
    End If
    Application.EnableEvents = True
End Sub

Well, I still can't get it to work. For one thing, VBA won't let me use "Round" in the code. Might be becasue I'm running an old version of excel. But, I'm not convinced that that alone is the problem. I inserted round functions into the two cells that the loop compares.

Working from sheet 11 (split batch mode). What the loop is supoosed to be doing is making the values for cells Z35 and Z37 equal each other. I'm beginning to think that this operation is not possible, but if you could take one last look at it, I would appreciate it. It's' been cooking my brain for 3 days now. I made some changes. Here is the link to the new sheet.

http://dl.dropbox.com/u/80752316/BBL...y%20Loop2).xls

Take a look at this part of your code.

Do While (dbLauter <> dbSparge)
    If dbLauter > dbSparge Then
        Sheet11.Range("FO503") = Sheet11.Range("FO503") - 0.001
    End If
    If dbLauter < dbSparge Then
        Sheet11.Range("FO503") = Sheet11.Range("FO503") + 0.001
    End If
Loop

Once the program goes into this loop, namely if dbLauter does not equal dbSparge, then there is no way out of this loop since nothing inside of the loop changes the values of dbLauter or dbSparge. Thus your code is stuck inside an endless loop.

I'm not sure I follow you. FO503 is a variable in the equations that determine the values of dbLauter and dbSparge. I can enter values manually for FO503 and make dbLauter = dbSparge, but the loop can't seem to do it.

I'm not sure I follow you. FO503 is a variable in the equations that determine the values of dbLauter and dbSparge. I can enter values manually for FO503 and make dbLauter = dbSparge, but the loop can't seem to do it.

That is the source of your confusion. A VBA double variable is not like an Excel cell containing a formula which will continually change its value as its source changes values. If that is the way you want it to act, you need to use a Range variable, and not a double, or the cell location itself. A Range can refer to one or more cells on a worksheet. A double, on the other hand, is just a container for a floating point number. The value of dbLauter & dbSparge will only change if you place it to the left of an equal sign: such as, dbLauter = dbLauter + .001 (or whatever).

I don't claim to understand the logic of your program, but I got the following to work. By work, I mean I got the program to start, and then the values of Z35 & Z37 equalized. I got it to work both with and without using Round.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    With Sheet11
        If Sheet3.Range("G11").Value = "Equal" Then
            Do While (Round(.Range("Z37").Value, 3) <> Round(.Range("Z35").Value, 3))
            'Do While (.Range("Z37").Value <> .Range("Z35").Value)
                If .Range("Z37").Value > .Range("Z35").Value Then
                    .Range("FO503") = .Range("FO503") - 0.001
                ElseIf .Range("Z37").Value < .Range("Z35").Value Then
                    .Range("FO503") = .Range("FO503") + 0.001
                End If
            Loop
        Else
            .Range("FO503") = CDbl(.Range("Z41").Value)
        End If
    End With
    Application.EnableEvents = True
End Sub

Since most of the references to cells (aka Range("")) was to Sheet11, I used a With Sheet11 statement. Thus every "Range", with a dot (period) before it and nothing else before it, refers to Sheet11.

Just tried it. Works like a charm!!!! You are the man. You just don't know how much I appreciate your help with this. I just feel bad that I have nothing to offer to return the favor. If you like craft beer and you're ever in the vicinity of Kokomo, IN give me a shout. There will be a case of primo homebrew with your name on it. That's what this program is for, brewing beer. Well, thanks again. Have a great weekend.

Cheers,

Clue_Less

I see. That makes a lot more sense. The first loop I got working only had one foating point number and the other was fixed. I had a feeling that since this loop had two of them, that changes whole ball game. No wonder I was having such a hard time with it. I wish I knew more about VBA, but I've never needed it before. I'll try the new code and see how how it works. I can't thank you enough for your help with this.

Clue_Less