How to Combine Two Loops

[Clue_Less]

I have two loops that work individually. I now want to combine the two. Here's what I want to do.

IF Sheet3.Range ("G11") = "Fixed" then run the first loop

If not, then IF Sheet3.Range ("G11") = "Equal" then run the second loop

If neither condition is met then = o

Here are the loop codes

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I want to omitt

Please Login or Register  to view this content.

from the second loop and

Please Login or Register  to view this content.

from the first loop because the loops themselves replace these values. Hopefully I explained that in a way that is understandable. Any help would be greatly appreciated.

Thanks,

Clue_Less

[StevenM]

I make no guarantees that this is what you want. I merely cut and pasted the information and tried to make only small modest adjustments.

Please Login or Register  to view this content.

As discussed earlier, if this code changes the value of Sheet3.Range("F12").Value,
the value of iQtsLb does not change.

[Clue_Less]

Cool. Thanks. I'll play around with it some more. It gets really confusing since this is all uncharted teriitory for me. Seems like everytime I try to manipulate the code myself I get error after error. I need to learn more about VBA, I find it very intersting (and frustrating since I'm ignorant).

[StevenM]

Given that your spreadsheet deals with one of the four most important beverages in life (the other three are: wine, Dr. Pepper, and water), I am most happy to help anyway I can. :-)

[Clue_Less]

I’ve run into a snag. I have the sheet finished and it doesn’t work right. It’s not wanting to run some of the code for some reason. It won’t run the loops and it doesn’t run the recorded macro (the work around for my glitch). Could you please take a look at it for me? I’ve double triple checked everything and can’t find anything out of place. Perhaps I’ve been staring it too long and am overlooking something obvious. Here’s a link to the sheet.

http://dl.dropbox.com/u/80752316/BBL...;t%20Work).xls

Here’s a brief overview of what the sheet should be doing. I’ve already entered values for you that are typical of how the sheet will be used. On Sheet1 these values are entered into cells “B23:H23” on Sheet11 values are in “B34:H34”. FYI, the cell that the loops should be generating values for is cell AB29 on Sheet1 and Cell AB40 on Sheet11.

With the above values entered, if cell “G11” on sheet3 = “Fixed” the following should be true.

Working from Sheet1

Sheet1 Cell “G35” = Sheet1 Cell “Z27” = Sheet3 Cell “F12”

Working from sheet11

Sheet11 Cell “G46” = Sheet11 Cell “Z38” = Sheet3 Cell “F12”


With the above values entered, if cell “G11” on sheet3 = “Equal” the following should be true.

Working from Sheet1

Cell “F30” = Cell “F31” and Cell “Z29” = Cell “Z30”

Working from Sheet11

Cell “F41” = Cell “F42” and Cell “Z40” = Cell “Z41”

Many Thanks,

Clue_Less

[StevenM]

Allow me to make some observations.

(Point 1) You have three: Worksheet_Change sub-routines in your Sheet1 Module. And they are not working at all!! Maybe it is possible to have more than one such sub-routine, but with my limited knowledge, it doesn't make any sense to me.

Just to check. I added:

Please Login or Register  to view this content.

And I added break points in the other sub-routines. Then when I changed a value in your worksheet, Worksheet_Change fired and I got the message "Hi!", but none of the other sub-routines fired. I might be wrong, but I doubt you can have: Worksheet_Change1, Worksheet_Change2, or Worksheet_Change3.

So the reason your loops are not working, is because they are not being triggered (aka started or fired or whatever you want to call it).

(Point 2) Worksheet_Change1 & Worksheet_Change2 don't use the value: Target. Which suggests to me that they shouldn't be worksheet sub-routines. You should move these sub-routines to a standard module, and call them when needed. Which leads me to my next point.

(Point 3) Why do you want to use worksheet_change to trigger your macros? Why not add a macro button called "run" and use that to trigger your macros?

[Clue_Less]

Point 1

In an earlier sheet, I had two functioning Worksheet_Change sub-routines, so I don't think that's the problem. Although, I'm certainly no expert so that's worth looking into.

Point 2

Ok. What would a standard module be?

Point 3. I could, but would prefer not to. The calcs should run automatically and that's the way I want them to run. However, since you got the msg box to pop up, it sounds like maybe the macro is running, but the other sub-routines are not, so the values are not changing simply because nothing is changing.

If you have a suggestion on how the change the sub-routines to something different, I'll give that a try.

[StevenM]

I combined your two loops into one sub-routine (which I called "TwoLoops"), since each loop was dependent upon the value of: Sheet3.Range("G11")

Please Login or Register  to view this content.

But I don't know that relationship between the above and Updatemash1
When do you want Updatemash1 to run?
When do you want TwoLoops to run?
Would you like one to run and then the other?

As for the difference between a Worksheet module and a standard module, updatemash1 was in a standard module. I guess it really doesn't make any difference where you put them.

Updatemash1 seems to delete the value at C12 and then move the window view.

[Clue_Less]

UpdateMash1 has no relationship with anything really. It is just a recorded macro that scrolls the page up, selcects cell c12, and then scrolls the page back down. This is the work around for the glitch I was talking about. For some reason, even though auto calculation is turned on, cells won't automatically update unless I scroll the page out of view of the cell I expected to update. In short, what you observed is what UpdateMash1 should be doing. I want updateMash1 to run anytime the values for cells "B23:H23" are changed.

TwoLoops should run whenever any change is made to the sheet.

[Clue_Less]

Correction for two loops.

Two loops should run whenever anything in the working sheet or sheet3 is updated. So, when working from sheet 1 for example, TwoLooops should run whenever anything on sheet 1 or sheet 3 changes.

[StevenM]

Then how about this?

Please Login or Register  to view this content.

[Clue_Less]

Ok, I'm diggin it, that's working. The loops work. Couple things. The page scrolled up, but didn't scroll back down when a value changed for cells "B23:H23". The whole idea is to make it look like nothing happened except for the values in the cells being updated. Could you 1: Either run the receorded macro here or 2: make it scroll back down to where it started? Also, how would I go about making the loops run when changes are made in sheet3?

[Clue_Less]

I substituted my macro for your coding that scrolled the page and that worked, so all I need now is how to make the loops run when sheet3 is updated.

[StevenM]

The page scrolled up, but didn't scroll back down when a value changed for cells "B23:H23". The whole idea is to make it looked like nothing happened except for the values in the cells being updated.

Hey, I was just cutting and pasting. I don't know how that works! :-)

how would I go about making the loops run when changes are made in sheet3?

Okay. So that would be the point of putting the code in a standard module, give it a name (such as "TwoLoops" or whatever). Then you can have a Worksheet_Change sub-routine from either sheet call this one sub-routine.

In the last version of the code, I took out all the dots before the (non-sheet3) Ranges because I figured that Sheet1 would be active when it ran (because it was inside the sheet1 worksheet module). If you move the code to a standard module, you might want to put back the dots and add With Sheet 1 ... End With as before.

Please Login or Register  to view this content.

Then in Module1 or any other standard module add:

Please Login or Register  to view this content.

Note: You have to remove the "Private" from before Sub, because you want other modules to call it.

[Clue_Less]

Cool. Thanks Man. I got it working. I didn't quite get the module thing (although I tried some things to no avail) so I decided to add a refresh button instead. I'm so glad to have it working finally. Now I can do some actual testing and make sure the program spits out the right numbers. You've been a great help. I'll leave you alone now

Clue_Less