Solved

Code works....sometimes....

Posted on 2014-01-22
19
246 Views
Last Modified: 2014-01-27
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?

thanks for your help!
Test10.xlsm
0
Comment
Question by:BostonBob
  • 10
  • 9
19 Comments
 
LVL 29

Expert Comment

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

Author Comment

by:BostonBob
ID: 39802731
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39802784
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39805763
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 Comment

by:BostonBob
ID: 39806964
I might have figured it out.  I have to try it later today.  Will let you know. thanks.
0
 
LVL 29

Expert Comment

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

Author Comment

by:BostonBob
ID: 39808314
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
 
LVL 29

Expert Comment

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

Author Comment

by:BostonBob
ID: 39808377
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 29

Expert Comment

by:gowflow
ID: 39810230
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.

So please sake good order,
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.

Ball in your court now.
Regards/gowflow
0
 

Author Comment

by:BostonBob
ID: 39810256
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39810342
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 Comment

by:BostonBob
ID: 39810396
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39810458
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 Comment

by:BostonBob
ID: 39810507
I will answer in English and then answer each of your questions below.

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.  

Your comments:

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.  :)

I FOUND YOUR SPECIFIC QUESTIONS ABOVE VERY HELPFUL AND i AM HOPEFUL YOU FOUND MY ANSWERS HELPFUL.

THANKS!
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39810585
YOur a tough one to understand and ego centric on top.

Please read English and reply clear.

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
 

Author Comment

by:BostonBob
ID: 39810638
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 Comment

by:BostonBob
ID: 39811125
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.

thanks again for your help.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39811299
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now