Macro not working as expected

[bknight]

Hi,

Alan, I have observed some strange behaviors...

me too! – some real strange behavior – a lot of seemingly drink people are throwing Fireworks around the streets here.
But I confess my observation skills are a bit blurred just now.
If my observation skills improve – probably around late tomorrow, I will take a look

LOL Ok, Get another beer and enjoy the fireworks. We're having champagne a little later, perhaps 8 hours behind you.

[Doc.AElstein]

Hi..

I observe some strange results based on the Before and After and code that you give .. Lol.. :)
It is difficult really to see what the problem is ( if there is one ) as I do not get the same After results as you

_1) This is what I get, with explanations of why….

_ 1a) According to your Before for Worksheets “Vix” and “PutCall Ratio” column B and F are filled to the same row. I assume then that this situation suggests no rows are to be added. ?
The code does not take care of the case for no rows to be added. What it does is give some stupid results ( see below ) . I expect that is not wanted.
( I mentioned here http://www.eileenslounge.com/viewtopic. ... 80#p197565" onclick="window.open(this.href);return false; one way to cope with that occurrence and to prevent anything being done in such a case. )

_1b) Similarly for Worksheet “OEX”, as entries in column C and H end in the same row, you have the same situation as in _1a).

_1c) I do not get exactly the same After results as you for the three worksheets mentioned above.
For example: You gave this for “Vix” After:
VixAfter_31Decbk.JPG http://imgur.com/WJB4W9P" onclick="window.open(this.href);return false;

VixAfter_31Decbk.JPG

I get this:
VixAfter_31DecAE.JPG http://imgur.com/zOz60Z8" onclick="window.open(this.href);return false;

VixAfter_31DecAE.JPG

The results I get are what I expect and are in line with the discussions _1a) and _1b)

I expect _1) above is not the main issue, but_..
_1(i) I just mention it in passing as you might want to consider a check in the code to deal with such a scenario.
_...and
_1)(ii) It is also helpful to look at the correct After for Worksheet “OEX”, to understand the results I do get
OEXAfter_31DecAE.JPG http://imgur.com/LZ3bard" onclick="window.open(this.href);return false;

OEXAfter_31DecAE.JPG

_......................

_2) The main problem as I see it with the code, at least based on the code version you gave and your Before.
The main problem is that in the code goes into an infinite loop in the “Calc” section

This is easy to explain. The date got, LDteVal2, ( as the last date for a fully filled row in “OEX” ) is ( based on your Before ( and also based on the last screen shot above) , 30.12.2016, ( in .Value2, this is 42734 ).
The following Loop, increases that date by one, ( one day is +1 in .Value2 ), and keeps Looping increasing the Array size.

Code: Select all

    Do
     ReDim Preserve SrchMtrxT(1 To 1, 1 To (UBound(SrchMtrxT(), 2) + 1))
     Let SrchMtrxT(1, UBound(SrchMtrxT(), 2)) = SrchMtrxT(1, (UBound(SrchMtrxT(), 2) - 1)) + 1
    Loop While Not SrchMtrxT(1, UBound(SrchMtrxT(), 2)) = LDteVal2

That above code snippet increases the size of that array to allow for ( and so then includes ) an extra date each time it loops. The problem is that the initial Array brought in from the existing Dates in Worksheet “Calc” already goes up to 30.12.2016, ( in .Value2, this is 42734 ). It increases that and then keeps increasing it until it gets to that initial value.
Of course it never will get to that value ! At least not in the capability of VBA which is based , possibly, on a flat universe/ time scale. What I mean is that if I start running from my house in one direction , until I get to my house, then I would get there after going around the globe. But I think, even if memory capability was infinite, the code would not eventually go through all possible dates and then come back to where it started, having reached the end of time, and then starting again at the beginning of time. Bit that is a theoretical discussion not too relevant here I think )

_....

I have no idea how you get the results in your After. I do not see how the code you gave , working on the Before you gave, can give those After results

_....




As I do not get the results you do, then a lot of the above may not be too relevant to the issue you are having. But IMO you should try to understand the code. Remember there are many ways to do what you want, depending on exactly what you want. Remember in particular the points I made around Here: http://www.eileenslounge.com/viewtopic. ... 20#p197707" onclick="window.open(this.href);return false;
The general …. ..Build big Array with all Formulas in it, chuck ‘em out in one go …. “way” of doing what you want is just what came out as we went along. I find it pretty and interesting, but I have never seen it done like that. I also had not seen your first initial way of doing it ever done either.
At that link http://www.eileenslounge.com/viewtopic. ... 20#p197707" onclick="window.open(this.href);return false; I showed some of the more usual ways.

_.............

I expect you probably need to check carefully what Befores and Afters you have and what code was applied to them. ( The After should either show either what you got, or what you want to get. Whichever is the case you need to state, otherwise I am afraid I get confused easily :(

Alan


P.S.

...Why all the dates prior to last working date(1/3/2017) were changed to values?...

_a) Your After does not actually show that.
But
_b) Generally I do expect that. The code scheme of mine brings the date values into an Array. It increases the size of that array to allow for ( and so then includes ) extra date values up until the last date corresponding to a full filled row in Worksheet “OEX”. That modified Array is then pasted out.
I did not have any special reason for doing it like that.
_c) I can see another possible problem here . I had not noticed that dates are missing out some days. ( That is to say not having continuous days listed). So probably that adding of dates should be done a bit differently anyway

[bknight]

It may be Box

https://app.box.com/s/0pc52dzfoi0g0700tlzwjxrvcq3fh3ab" onclick="window.open(this.href);return false;

The line 3195 1/3/2017 has formulas, line 3194 12/30/2016 has values, as do all the previous lines.

[bknight]

Hi..

...

_2) The main problem as I see it with the code, at least based on the code version you gave and your Before.
The main problem is that in the code goes into an infinite loop in the “Calc” section

Uum, this is the code you and I worked out, and It has been working on a day to day basis. It may have failed last week and I just corrected the extra days added, I don't remember

This is easy to explain. The date got, LDteVal2, ( as the last date for a fully filled row in “OEX” ) is ( based on your Before ( and also based on the last screen shot above) , 30.12.2016, ( in .Value2, this is 42734 ).
The following Loop, increases that date by one, ( one day is +1 in .Value2 ), and keeps Looping increasing the Array size.

Code: Select all

    Do
     ReDim Preserve SrchMtrxT(1 To 1, 1 To (UBound(SrchMtrxT(), 2) + 1))
     Let SrchMtrxT(1, UBound(SrchMtrxT(), 2)) = SrchMtrxT(1, (UBound(SrchMtrxT(), 2) - 1)) + 1
    Loop While Not SrchMtrxT(1, UBound(SrchMtrxT(), 2)) = LDteVal2

That above code snippet increases the size of that array to allow for ( and so then includes ) an extra date each time it loops. The problem is that the initial Array brought in from the existing Dates in Worksheet “Calc” already goes up to 30.12.2016, ( in .Value2, this is 42734 ). It increases that and then keeps increasing it until it gets to that initial value.
Of course it never will get to that value ! At least not in the capability of VBA which is based , possibly, on a flat universe/ time scale. What I mean is that if I start running from my house in one direction , until I get to my house, then I would get there after going around the globe. But I think, even if memory capability was infinite, the code would not eventually go through all possible dates and then come back to where it started, having reached the end of time, and then starting again at the beginning of time. Bit that is a theoretical discussion not too relevant here I think )

I believed that you used "OEX" data as the last date, not "Calc". If this were the case then only dates would be added upto the "last" date that had values in "OEX", or "VIX" or "PutCal Ratio" as they will always have the same last date's data.

_....
_....




Alan


P.S.

...Why all the dates prior to last working date(1/3/2017) were changed to values?...

_a) Your After does not actually show that.
But
_b) Generally I do expect that. The code scheme of mine brings the date values into an Array. It increases the size of that array to allow for ( and so then includes ) extra date values up until the last date corresponding to a full filled row in Worksheet “OEX”. That modified Array is then pasted out.
I did not have any special reason for doing it like that.

OK, I just didn't understand that, but now that you have explained, I can live with that part.

_c) I can see another possible problem here . I had not noticed that dates are missing out some days. ( That is to say not having continuous days listed). So probably that adding of dates should be done a bit differently anyway

This is always the case Saturdays and Sundays are missing in every week, as there is no data for those days.

[bknight]

For completeness the start conditions. This Should have data for 1/3/2017, only with no calculations.

https://app.box.com/s/r8zvw43s3aku8i30vp8plftgxro3fnrr" onclick="window.open(this.href);return false;

[Doc.AElstein]

Hi
_............


I cannot completely say for sure that box never mixes anything up, but I think it is very unlikely.
( I often mix up my File and Image Links***
I have never known such a problem to lie in a File sharing site. )

_.....
As far as the After Files are concerned: The two box files for After and the screenshot here http://www.eileenslounge.com/viewtopic. ... 40#p198973" onclick="window.open(this.href);return false; are all identical.
( As explained they are not the results I get when running the latest code you gave based on the Before ( Start ) File you gave. )


The latest Start you gave http://www.eileenslounge.com/viewtopic. ... 40#p198976" onclick="window.open(this.href);return false;
is different to the one you gave before ( as Before ). . http://www.eileenslounge.com/viewtopic. ... 20#p198920" onclick="window.open(this.href);return false;
( The link is the same but the file is different now, I think )
So I will try the code again on that latest Start / Before File , if I get time, later .

_...........

Start / Stop dates etc..
I do use "OEX" data as the last date, not "Calc". This is the date the Do While Loop tries to “get to” by looping and increasing the date by one each time. The problem is the last date in the Start for "Calc" was at this date. I just happened to write the code to start at this date.
No special reason.
I guess it is normal development in code development to check / adjust for all scenarios. I will bear that in mind if I take another look later at the code with the new start File.

Alan

P.s. ***After you post , please click on and download or view any Files or Images you upload and check they are the ones you intend, thanks.

[bknight]

Further testing today reveled the following.
1. Adding additional days calculation on "Calc" only occurs during the first day, with four days of no data.
2. If the last date in "Calc" >= last date on any of the other sheets, "Calc" goes into an infinite loop as Alan described.
3. Deleting those error(added) dates on 'Calc" and adding one day at a time, on the other sheets, before execution result in normal operation.

[Rudi]

Hi bknight,

This thread has become very long and makes it rather difficult for anyone else to jump in and offer assistance without having to backtrack through the long history of correspondence. You'd probably find that you can acquire faster solutions if you focus on one specific issue at a time and post a short description of your problem with the necessary supporting file or code. This allows anyone to easily jump on the bandwagon with a solution or an idea based on your query and result in faster resolution for that one issue.

I understand that this project you are coding is large, but even large projects (and/or code) can be trimmed down to present a singular problem and request a singular result. It is just my opinion, but you might want to consider starting a new (shorter) thread(s) if you want to involve others, and draw from a greater pool of interaction. Having said this, I do not want to disregard the time and patience that Alan has shown with the help he has offered you over the course of this thread.

[bknight]

Rudi:
In the beginning God create the heavens and the Earth--no no no no not that far back, sorry. I had a four part macro that ran flawlessly in Excel 2000 for over 5 years. Then when I was forced to use Excel 2007, the macro did not function properly and my question was why and how to fix it. In between then and now we have come a long ways and covered a great deal of ground and code. To be honest I get lost, myself, so I have concentrated on the now.

I do understand what you are saying and I wished it would have been a simple fix of a to make the code run again, but that didn't happen. The original code resides only here as I have moved on. Should I have further issues with it I will start a new thread, my apologies for making this one so long.

[Doc.AElstein]

Hi bk,
I think
_..originally we discussed that your original code would have only worked for 1 line.. ( at least for Worksheet “Calc” – I think you said for more than 1 row of data you did that Worksheet manually ) the issue seemed initially to be a code that would work generally for many lines…
_..The very simple fix to your original code lies in the more conventional Copy Paste ways I explained and demonstrated, …. but never mind all that _..
_”..concentrated on the now…”

_............ To that end_....


Coming back to the latest code and the latest Files
What happens for first three Worksheets

So with the new ( correct ) Start, I get the same results as you. For example here is the start for “Vix”
VixStart_1Jan.JPG http://imgur.com/T01q7s8" onclick="window.open(this.href);return false;

VixStart_1Jan.JPG

_.., then, as expected that empty region ( I highlighted it in yellow above ) will be filled in correctly by the code. Then you get as in all your Afters
VixAfter_31Decbk.JPG http://imgur.com/WJB4W9P" onclick="window.open(this.href);return false;

VixAfter_31Decbk.JPG

The same goes for Worksheets “PutCall Ratio” and “OEX”

_........................

Now what happens for Worksheet “Calc”
The last date got from “OEX” from this
Let LDteVal2 = ws.Cells(Lr, 1).Value2 ' .Value 2 date Number for last Filled in row
_.. is 42738 ( It actually looks in the spreadsheet like 03.01.2017 ).
Now looking back at this recent Post http://www.eileenslounge.com/viewtopic. ... 40#p198972" onclick="window.open(this.href);return false; – We had for this date, , LDteVal2 , ( looking like 30.12.2016 in the spreadsheet), was 42734 as .Value2.

So now, as an aside, look at this little demo of “date what we see - .Value2”
12.30.16 - 42734
12.31.16 - 42735
01.01.17 - 42736
01.02.17 - 42737
01.03.17 - 42738

Our Do While is going to do what it was intended and keep adding dates until it gets up to 01.03.17 – 42738. ( In other words it Loops 4 times now ( and not infinitely ) until 42734 gets increased to 42738
DoWhileDidIt.JPG http://imgur.com/zl51oOy" onclick="window.open(this.href);return false;

DoWhileDidIt.JPG

So the code works as intended. The fact that you get a lot of 00000 stuff should be obvious: You have no data above 12.31.16 – ( 42735 ) .
_ – As you said yourself , you have some bum dates in the Worksheets: So instead of 12.31.16 – (42735) you have 01.03.17 – (42738)
If the dates were correct you would get just the one extra line added to Worksheets Calc. ( as you would just increase dates to 42735 )

I tried it: -
If you have the correct dates in OEX _...
CorrrectDatesOEX.JPG http://imgur.com/3VifrQW" onclick="window.open(this.href);return false;
_.. then you almost get the correct Calc
CalcAlmostThere.JPG http://imgur.com/eDVjAmI" onclick="window.open(this.href);return false;

_.. If you put the correct dates also in PutCall Ratio, then the formula in A3195_...
='PutCall Ratio'!A3326
_.. will also get the correct date and the final results are perfect.

_.....................................

I think, ( just IMO ) , you are running ahead of yourself, tripping over , running before you have learnt to walk. Etc.. etc.. In other words, just not going through and understanding what is going on and then as a result of that not only making simple mistakes.. but also big ones like saying this:

....Added next weeks dates and added some numbers, Note formulas in Column 1 on "Calc" (Note messed up on the dates, kept repeating, but not an issue, fix later)....

If you followed and understood at least a bit of the code you would realize that the dates need to be correct and so are the issue for the code to work for “Calc” Worksheet …..

Think about it:
Let LDteVal2 = ws.Cells(Lr, 1).Value2 ' .Value 2 date Number for last Filled in row – I must have said and written that a few times now. ( Just to spell out the problem again – the date for the last filled row was the incorrect bum date of 01.03.17 - 42738. Correcting that and the last few "issues" are mostly gone


_..........................


Anyways…
I think…
_1) I have a pretty good idea exactly what you want. ( And there are many ways to achieve it ). Most of them I have done for you and explained in detail. Close to all the information you need is already in this Thread. It is not actually a large code, at least not when stripped down to the minimum. I am only a novice, but I find the code very simple. It is one of the few codes in recent months in this Forum that I could understand

_2 ) The issues are being confused and cluttered up a bit by different data , bum data, different codes and different Files which are not always tying up.

_3) In my opinion it would help if you carefully review again the whole Thread. Otherwise if you start a new thread before doing that , I expect you will just go over everything again and need just as big a thread, ( if not bigger, If I do not catch it , as I already know what is going on and wanted. )

_4) If you do want to start another Thread and give up with this one, that is your decision. But I expect if you carefully review this one , and be a bit more careful with the data Files etc. that you give, then the thing should be pretty well in the bag. I think probably mostly just two issues now:
_(i) Code needs to be changed due to the non inclusion of Week Ends issue
_(ii) I expect there may be some jumbled up date format issues somewhere.

Alan