# Code works....sometimes....

Hi All,

I have attached a sheet.  A walk thru of the sheet will best demonstrate my problem.

On tab "Blue", place a "1" into F10.  A "1" will be evaluated into H10 by formula and in turn the vba code will make a "1" appear in I10.  So far so good and this work all the time.

The code will also make a "1" appear in Tab "Red", w10.  In turn a "1" will be evaluated into Tab "Red" Y10 by formula and in turn a "1" will appear in Tab Red z10....sometimes.

Repeating this process again tab "Blue" starting at say F14 everything will work again INCLUDING Red Z10, if it did not already get a "1" in it from before.

So I am not sure why the code works sometimes and does not work at other times.

As for the other code in the spread sheet putting "preini", "brains", and "basic" it is just there to show that something happens.  This is just filler and not the focus of this question.

The question or goal is to figure out how to get the entire row of code to execute the first time and by itself without the help of other rows.

ALSO WHEN I isolate and test just the Red or Blue code by itself (i.e. either the Red runs and Blue is commented out or vice versa) the code works just fine.  It just does not want to work together.

Any ideas?

Test10.xlsm
###### Who is Participating?

Commented:
YOur a tough one to understand and ego centric on top.

I see values and formulas in all of Columns
S --> formula = IF(Qot!V11>Con!AK11, 1, 0) (Who puts it VBA or manually ?)
U --> formula = IF(Con!AO11>Con!AM11, 1, 0) (Who puts it VBA or manually ?)
V -->  value  1 who puts it VBA or manually ?
W --> formula =IF(S11*U11*V11*V11*V11,1,0) (who puts it VBA or manually ?)
Y --> formula = IF(En!W11>En!X11, 1, 0) (who puts it VBA or manually ?)
Z -->  Value 1  who puts it VBA or manually ?

Answer each line VBA or Manually !!!

gowflow
0

Commented:
Try this. I removed the enableevent from both change events as it was preventing execution of code.
gowflow
Test10.xlsm
0

Author Commented:
Hi Gowflow,

That solution works for my test sheet but not so good on my real sheet with all the supporting code.

On the real sheet with the supporting code all that happens is the computer freezes up and seems to go into an infinite loop.

I don't get any of that with the EnableEvents--even though it only works half the time.

Any other thoughts?

thanks,
0

Commented:
well can you post the real sheet with all the code but muck up the data if confidential. As for sure when you post part of a code we cannot troubleshoot but what we see !!!
gowflow
0

Commented:
I did not see your comment to my  last comment. Are you still interested in finding a solution ?

I propose we deal with each question separately and don't mix issues. As we started with this one, lets continue with this question till closure then will move on to your other concern reason why I did not comment on the other question. I have solutions for you but lets try not to mix issues.

gowflow
0

Author Commented:
I might have figured it out.  I have to try it later today.  Will let you know. thanks.
0

Commented:
So are you ok with your issue or still need some help ?
gowflow
0

Author Commented:
Not sure.  Working on it right now.

What I thought worked doesn't.  Trying a few other things.   I am not so hopeful.  Probably be back to you pronto.

thx
0

Commented:
ok fine, don't try to break your head too much, we are here for that !!! :)
gowflow
0

Author Commented:
Ok.  I give up.  I seem to be like a dog circling around his mat to lay down but just can't quite get there.

I surrender!  I gave it a good college try to figure out this thing on my own but to no avail.

I have included a newer sheet.

Here are the things that I have discovered.

--If I comment out "disable events" but keep only the screen updating false/true it works on this test11.  It even works on my real spreadsheet--except that it takes about 20 seconds to process.   Since I am going to be doing hundreds/thousands of automated calculations per seconds who knows when the event(s) will fire and I am sure to crash the system.

--If you notice the "commented out" code that I have I have found that the overall code seems to work better if I immediately "reset" the row of code after I get to a "1" in the Z column in the En Sheet.

I would ideally like to keep the Enable Events = False/True because it makes it work much faster.

Is there a work around?

Maybe use a timer for 1 second to delay the end of the first event to the beginning of the second event.

Thanks!
Test11.xlsm
0

Commented:
Well lets agree on 1 thing:

You ask a question, and I mean a question or a problem or whatever

AND

You leave us to decide how to solve your question, problem or whatever for you.

I am in no way going to enter in this bazar of enable/disable or whatever discussion on any code that you may have tried or not and this NOT KNOWING what you want.

Ask a question on what you intent to achieve and not how you envision the solution to be and I will be happy to help you.

Regards/gowflow
0

Author Commented:
Sorry, didn't mean to make you angry.

Just want it to work.  The question is the same.  The code only works sometimes.

When I disable the EnableEvents it works but only for a small sheet and it still takes way too long (20 seconds +).  For the larger sheet it just crashes.

ANY solution is terrific.

So again I want the code to flow so that the two events will work sequentially.

thanks.
0

Commented:
FORGET THE BLOODY CODE !!!!

What the .... you want ???
please say it in English !!

I have a sheet that have xys then I want to be able to put F in A then when abc is met then have this xyz ... blablabla

Pls give me in English a detailed explanation of what you are trying to achieve otherwhyse I am totally sorry but we are chasing our tail you want me to think in the same box you are in !!!
NO Sorry

gowflow
0

Author Commented:
On Test11 (attached a few comments up):

Sorry you are frustrated with me.  That is NOT my intention.  Remember I trying to respond the way you want me to but my non-programming brain sometimes takes a few extra tries to get to where it needs to go.  Having said that I think (I hope) this is what you want.

Put a "1" in F10 on sheet "Pre".  It will flow thru to Z10 on Sheet "En" with a "1".  (There is extra code executing with this event but that is just filler and not the issue.)

Same for F14 on sheet "Pre".  It will flow thru to Z14 on sheet "En" with a "1"

...and so on for the entire range.

Enable Events was commented out.

On this small sheet it seems to work fine and fast.  On my much larger sheet, it still works, but is very slow.   I need it to go faster.  Much faster.

Is there a way to make the vba code flow much faster?

Thanks.
0

Commented:
You are so much taken in your code that you still don't get it !!!

Please forget all the code that is there, repeat FORGET ALL THE CODE THAT IS THERE !

Tell me what you want to do and what do you expect the result to be. Full stop.

What I gathered is the following:
In sheet Pre
-----------------
I want to put a value (I presume 1) in F11 (or any row in that column and then, I expect to have ??? What ? I think you want 1 put in I11 (then I see a formula in H11
=IF(F11>G11,1, 0)
this formula I want the macro to put it ?? or it is there ???

then by putting this 1 in sheet Pre of F11 I want
In sheet En
---------------
I see values and formulas in all of Columns
S --> formula = IF(Qot!V11>Con!AK11, 1, 0) (Who puts it VBA or manually ?)
U --> formula = IF(Con!AO11>Con!AM11, 1, 0) (Who puts it VBA or manually ?)
V -->  value  1 who puts it VBA or manually ?
W --> formula =IF(S11*U11*V11*V11*V11,1,0) (who puts it VBA or manually ?)
Y --> formula = IF(En!W11>En!X11, 1, 0) (who puts it VBA or manually ?)
Z -->  Value 1  who puts it VBA or manually ?

NOW
if you say VBA then you need to tell me why
if you say formula you need to tell me why

I hope you still have enough courage to answer this. I am willing to take this to the end. You cannot expect us to jump in your code not knowing what you want. Maybe there is a much easier way to get what you want that you didn't think of

REASON why we need to know what you want and why all these 1 and 0
if you don't mind.

gowflow
0

Author Commented:

Thanks for sticking with it to the end.

In English:

I have dynamic data from another source streaming in for this code on another sheet (not shown).   So everything that happens on this sheet will be done automatically.

I have the overall goal of when a "1" appears in a certain place it fires code off and if it is a "0" then nothing happens.  So in short my code is just a series of "1" and "0".

The overall code/sheet that I have is just a series of these events but I am having trouble with the two events firing only...that is not taking a long time to fire sequentially....especially with the bigger sheet.

I will answer it all in BOLD.  (NOT YELLING  :)

What I gathered is the following:
In sheet Pre
-----------------
I want to put a value (I presume 1) in F11 (or any row in that column and then, I expect to have ??? A "1" GOES INTO H11 AND THIS IN TURN MAKES  "1" APPEAR IN I11.  FROM WHAT I HAVE LEARNED SO FAR THAT THIS IS ONE WAY TO MAKE A AUTOMATED EVENT HAPPEN BECAUSE THE "1" WILL APPEAR IN I11.  FROM HERE A WHOLE BUNCH OF CODE CAN RUN.

THIS THING WORKS FINE IN AND OF ITSELF. THE PROBLEM ARISES WHEN TWO OF THESE EVENTS FIRE OFF SEQUENTIALLY AND IMMEDIATELY (MORE ON THIS AT VERY END OF THIS POST).   IT JUST IS NOT FAST ENOUGH AND NEEDS TO BE FASTER.

What ? I think you want 1 put in I11 (then I see a formula in H11
=IF(F11>G11,1, 0)
this formula I want the macro to put it ?? or it is there ???   CORRECT.  AGAIN, THE PROBLEM IS TO GET THIS H11 AND I11 AND IN TURN Y11 TO Z11 ON SHEET 'EN' TO WORK SEQUENTIALLY AND IMMEDIATELY.   ...AND SO ON FOR EACH ROW.  YES. CORRECT.

then by putting this 1 in sheet Pre of F11 I want
In sheet En

ALL OF THIS IS DONE AUTOMATICALLY/VBA BUT IT IS NOT THE ISSUE.  I HAVE NO DIFFICULTY FIGURING ANY OF THIS CODE IMMEDIATELY BELOW OUT.
---------------
I see values and formulas in all of Columns
S --> formula = IF(Qot!V11>Con!AK11, 1, 0) (Who puts it VBA or manually ?)
U --> formula = IF(Con!AO11>Con!AM11, 1, 0) (Who puts it VBA or manually ?)
V -->  value  1 who puts it VBA or manually ?
W --> formula =IF(S11*U11*V11*V11*V11,1,0) (who puts it VBA or manually ?)
Y --> formula = IF(En!W11>En!X11, 1, 0) (who puts it VBA or manually ?)
Z -->  Value 1  who puts it VBA or manually ?

I HAVE PLENTY OF THIS KIND OF CODE ABOVE IN THE OTHER SHEETS AND AGAIN IT JUST ISN'T THE ISSUE.  THIS CODE IS SIMPLY CHECKS AND BALANCES THAT GO INTO OTHER PARTS OF THE SHEET TO MAKE SURE CONDITIONS ARE MET.  BUT SHOULD NOT BE THE ISSUE OF YOUR ANALYSIS/HELP.

NOW
if you say VBA then you need to tell me why
if you say formula you need to tell me why

I hope you still have enough courage to answer this.  I AM GETTING A BIT GUN SHY BUT WILL PRESS ON.    I am willing to take this to the end. You cannot expect us to jump in your code not knowing what you want.

Maybe there is a much easier way to get what you want that you didn't think of.  YES, THUS MY QUESTIONING THIS POSSIBILITY ON THE OTHER POST AND HERE.   IS THERE A BETTER WAY--THAT WOULD BE ENTIRELY AWESOME!!?   KEEP IN MIND THAT I HAVE BEEN DOING VBA CODE NOW FOR JUST OVER A YEAR AND THERE IS SO MUCH TO LEARN AND THERE ARE A BUNCH OF HOLES IN WHAT I ALREADY KNOW SO I AM NOT EVEN SURE HOW TO PROPERLY ASK THE QUESTIONS AT ALL.

REASON why we need to know what you want and why all these 1 and 0
if you don't mind.    PART OF A SHEET THAT 'DOES SOMETHING' WHEN A 1 APPEARS, DOES NOTHING WHEN A 'O' APPEARS.  IT IS JUST A BIG OLD DECISION TREE THAT IN TURN LOOPS UP TO THE BEGINNING (WHEN CERTAIN CONDITIONS APPEAR).

THANKS FOR FOLLOWING THRU.

YOU PROGRAMMERS ARE BRILLIANT AND SOMETIMES I FEEL LIKE I AM GOING BEFORE THE GREAT OZ FOR ANSWERS TO MY ANNOYING QUERIES.  :)

SO IN THE END THE ONLY REAL PROBLEM IS HOW TO MAKE THIS THING:

Private Sub Worksheet_Calculate()
Dim rngUpdate As Range
Dim sFormula As String
On Error GoTo NoRangeFound
sFormula = "If('" & Me.name & "'!H10:H250=1,Row('" & Me.name & "'!H10:H250)&"":""&Row('" & Me.name & "'!H10:H250))"
Set rngUpdate = Me.Range(Join(filter(Application.Transpose(Evaluate(sFormula)), False, False), ","))
If Not rngUpdate Is Nothing Then
Set rngUpdate = Union(rngUpdate, rngUpdate)
Intersect(rngUpdate.EntireRow, Sheets("PreIniEntLong").columns("I")).value = 1
Set rngUpdate = Nothing
End If
NoRangeFound:
End Sub

WORK SEQUENTIALLY AND IMMEDIATELY WITH THIS THING:

Private Sub Worksheet_Calculate()
Dim rngUpdate As Range
Dim sFormula As String
On Error GoTo NoRangeFound
sFormula = "If('" & Me.name & "'!Y10:Y250=1,Row('" & Me.name & "'!Y10:Y250)&"":""&Row('" & Me.name & "'!Y10:Y250))"
Set rngUpdate = Me.Range(Join(filter(Application.Transpose(Evaluate(sFormula)), False, False), ","))
If Not rngUpdate Is Nothing Then
Set rngUpdate = Union(rngUpdate, rngUpdate)
Intersect(rngUpdate.EntireRow, Sheets("EntryLong").columns("Z")).value = 1
Set rngUpdate = Nothing
End If
NoRangeFound:
End Sub

AGAIN THESE TWO SETS OF CODE WORK FINE ON THEIR OWN BUT WHEN THEY BOTH FIRE IMMEDIATELY AND SEQUENTIALLY IT DOES NOT FIRE FAST ENOUGH.  THAT IS THE PROBLEM.   ON MY OTHER LARGER SHEET IT TAKES ABOUT 20 SECONDS TO FIRE.  YOUR PREVIOUS SUGGESTION TO COMMENT OUT ENABLEEVENTS WAS GOOD/GOLDEN IN THAT THE CODE NOW WORKS TOGETHER AS BEFORE IT WOULD NOT WORK TOGETHER AT ALL.   HOWEVER, EVEN THOUGH THE CODE WORKED TOGETHER IT WOULD NOT WORK FAST ENOUGH.

THAT IS NOT ME BEING UNGRATEFUL.  YOUR SUGGESTION CERTAINLY MOVED ME FORWARD WHEREAS I COULD LITERALLY NOT FIGURE IT OUT MYSELF FOR DAYS AND DAYS ON END.

THE STREAMING DATA THAT I HAVE COMING IN THE REAL SHEET IS 1000 ROWS LONG AND I JUST WANT TO SEE IF THERE IS A BETTER WAY IF YOU CANNOT SUGGEST SOMETHING FASTER.

I AM AWESTRUCK THAT YOU GUYS CAN LOOK AT MY STUFF AT ALL AND MAKE SENSE OF IT.

THANKS SO MUCH FOR KEEPING GOING.

IF YOU HAVE MORE QUESTIONS OR WANT TO GROWL AT ME MORE THEN PLEASE DO SO.  i WILL GO ALL THE WAY UNTIL THE END TOO.  :)

THANKS!
0

Author Commented:
I see values and formulas in all of Columns

S --> formula = IF(Qot!V11>Con!AK11, 1, 0) (Who puts it VBA or manually ?)
VBA

U --> formula = IF(Con!AO11>Con!AM11, 1, 0) (Who puts it VBA or manually ?)
VBA

V -->  value  1 who puts it VBA or manually ?
VBA

W --> formula =IF(S11*U11*V11*V11*V11,1,0) (who puts it VBA or manually ?)
VBA

Y --> formula = IF(En!W11>En!X11, 1, 0) (who puts it VBA or manually ?)
Manually

Z -->  Value 1  who puts it VBA or manually ?
VBA

thanks!
0

Author Commented:
Gowflow,

I figured it out.  Thanks for helping.  Sorry you got frustrated with me.  Not my intention at all.

The solution was moving Y-->  outside the EnableEvents = true

I can see now why you were not pleased me with.

Truly I was not trying to be J**K.

0

Commented:
Great glad you were able to and tks for the points. See it is always good to go back to the source and see why we have what we have then we can dissect and find solutions as sometimes I also in a complex routine find myself going in circles and it is only when I scratch everything and ask myself what do I need to achieve and start from zero that I find the solution to my problem.

Sorry if I sounded pushy, but I noted in you same aggressive attitude that I sometimes (often) have that is also detrimental to me reason why I was pushy for you not to make same mistakes that I sometimes do !!!

:)
Have a nice day and pls do not hesitate to put a link in here I will keep this question monitored if you need help in any other issue.
Regards/gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.