Macro not working as expected

[Doc.AElstein]

Hi
As promised a demo of the other more typical ways of doing what you want.
I will only do the case of the worksheet “Vix” The code will work on this version of one of your files.
https://app.box.com/s/nhrf73dqdq7xbphxw2rp9sf859yfcius" onclick="window.open(this.href);return false;
I have not kept to date with which formulas you are using currently, but for demoing the different ways that is not too important
As I mentioned, it will not deal with the problem of, Sr. – Any code will need to have a way for determining that. As I am just demoing for Worksheets Vix , I assume the logic of _2a)
http://www.eileenslounge.com/viewtopic. ... 68#p197668" onclick="window.open(this.href);return false;
I modified it to work over 3000 rows. That is to say I just emptied 3000.
So this is your start:
Vix18Start.JPG http://imgur.com/WZjGhcm" onclick="window.open(this.href);return false;


When the codes have finished, 3000 lines of formulas are added in columns F – I

I will only very briefly describe the ways for now. I do not think it would be wise to give a full description here now, or I will not get my Xmas . If you want more specific help on understanding a particular way or Method, than I am sure one of us will be able to help further.
I put the code here, just to keep the clutter here down a bit.
http://www.eileenslounge.com/viewtopic. ... 01#p197701" onclick="window.open(this.href);return false;


Short Description as Briefly as I am able for the different “ways to do it”, ( That I know about )
( I could probably think of a few more)
( some variables are not used, - it is basically the full code with the extra “ways to do it” but only as far as when Worksheet Vix is finished with.

Rem 1) Build big Array with all Formulas in it, chuck ‘em out in one go
Basically like the last few code versions we have been playing with

Rem 2) Copy Paste ways.
Your very first code way could fit in here, but is more common to do the first few of these:
'2a), '2b), '2c)
All variations on a theme. For these we do need to Copy a Range of existing formulas, - as you originally did. But the difference is that we do it once.
You know I think, that if you copy a single formula line ( with relative type Address in it ( like without the $’s) ) , then if you paste over the whole final range, then you get the formulas adjusted.
Here more info on that
http://www.eileenslounge.com/viewtopic. ... 44#p196723" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 44#p196735" onclick="window.open(this.href);return false;
and in particular the different ways are here in more detail
http://www.eileenslounge.com/viewtopic. ... 02#p193871" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 02#p195791" onclick="window.open(this.href);return false;

'2d) ' Copy row Loop rows.PasteSpecial each row
This is a sort of “halfway house” between the usual normal ways above and yours. I include it as I did have an occasion when apparently the above methods failed when the Range was very big. This way then did work
http://www.excelforum.com/excel-program ... shing.html" onclick="window.open(this.href);return false;
Like your code it pastes in one line at a time, but only copies the once.

Rem 4) ' column Letter and row Number notation .Formula (.Value) Property
This is often overlooked and Rem5) is used instead as Rem5) looks more like the thing it does. Bit this does the same, ( I think )
Often in VBA, if you give a Formula in relative references to more than one Cell then it works like '2a), '2b), '2c). The difference being it is working on the Range Object “values” as opposed to the full Range Objects in Rem 2) ways . It might then be quicker. Disadvantages might be problems again with the things like the date formats

Rem 5) ' R C notation .FormulaR1C1 Property
Like Rem 5). But looks more like it is doing what it is doing.
See here for some info on the R C type notation.
http://www.eileenslounge.com/viewtopic. ... 68#p194421" onclick="window.open(this.href);return false;
http://powerspreadsheets.com/r1c1-formular1c1-vba/" onclick="window.open(this.href);return false;

Rem 6) ' Evaluate Range way.
An old favorite of mine. Very briefly. You build up a your formula as a string using a combination of simple string text and VBA things. The Evaluate( ) Method takes a sting and “evaluates” it as it would effectively be done in a Spreadsheet. There can be some advantages, such as if you want your final formulas to be absolute ( have the $ bits included )
http://www.eileenslounge.com/viewtopic. ... 68#p194481" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 98#p196259" onclick="window.open(this.href);return false;

_

I am off for a bit now. I cannot think of any more excuses not to write my Xmas Cards.
Alan

[bknight]

Take the after spreadsheet and drag an drop the last row on "Calc" down, you will see what I mean.
What I was thinking was to evaluate the array members and then adjust the upper bound of the array if one or more values were zero as the zeros if any would be at the upper or lower. I rather took it as when the Let rngWs.Value = arrrngWs() is execute all the members get input, so address the members, before executing that step.

[Doc.AElstein]

Hi
The Array is built up with the formulas. The members of the Array look just like the Strings as you see printed out in the Debug.Print
or from the screenshots like here
http://www.eileenslounge.com/viewtopic. ... 80#p197548" onclick="window.open(this.href);return false;
or as you see it in the formula bar
or as you can see in the Watch Window for any Array like this
arrVixWatchWindow.JPG http://imgur.com/2d6azXY" onclick="window.open(this.href);return false;

arrVixWatchWindow.JPG

There is never any evaluating done in the Array. You only ever have strings formulas ( or string links ) in the Array . The purpose of the Loop is to build all the formula strings ( or string links ) for each row and put them in the Array. At the end of the Loop the Array is full with all your String Formulas ( or string links)

It pastes all its formulas ( or links ) into the Worksheet ( all in one go at Let rngWs.Value = arrrngWs() ) *****

Those formulas are then in the Cells.
Or rather Excel has actually for each cell a Range Object as I showed here: ( There I showed you it for RngVix – but it looks similar for every single cell )
http://www.eileenslounge.com/viewtopic. ... 80#p197521" onclick="window.open(this.href);return false;

In that Cell Range Object are their values, the string Formulas in ( various Forms: - the column letter and row number notation; and the R C type notation etc.. etc… ) the .Value , .Value2 and god knows what else.
As I mentioned before that Range Object is massive.

When you “Paste “ all those Formulas in with Let rngWs.Value = arrrngWs(), what actually happens is that the Range Object gets a few things filled in like all its various “values” - .Value, .Formula etc.. etc..

That Range Object and all it has in it is held somewhere in Computer cyber space.

Excel arranges that you see the .Value in the screen displayed grid ( what you see as a spreadsheet), and it arranges that you see the column letter and row number notation Formula in the formula bar. All the other various bits of the range object you mostly do not see ( you would see some things , like background color and the such if it had such )

Any evaluation, as such, is done as the Range Object gets Filled at Let rngWs.Value = arrrngWs().
After this Line the Array , arrrngWs(), is still filled with the Strings. *****So maybe it would be better to say that at Let rngWs.Value = arrrngWs() those string values in the Array are copied to the Range object
At no point ever are any evaluated values returned to the Array. It only ever has the strings in it which are the strings you put in it in the Loop

The Array is totally independent to the Worksheet at all times. You use ( copy ) those strings at Let rngWs.Value = arrrngWs(). But you never change the string values in the Array

_....
So if I understand this..

... I rather took it as when the Let rngWs.Value = arrrngWs() is execute all the members get input, so address the members, before executing that step.

… That will not work as… what actually happens is the Members of the Array which get Input are string formulas or string links. So addressing the members before will be addressing strings formulas or string links. The values, as such, are what Excel displays to you once those formulas or strings in the Array ( copies of ) go into the Range Object. You can never see the value in the Array as there are never values in the Array . In the Cell is everything, but Excel just shows you a few of the Range Object ( Cell ) "things" ( Properties )
( A Cell is a Range Object, just as many cells are another Range Object. A Range Object is a collection of one or more Cells )

[Doc.AElstein]

You could use the Evaluate( ) Method to evalute any formula ( or link ) string.. But doing that for every Formula ( or link ) string might have time implications for large Arrays with many Members

[bknight]

Alan:
You asked a question concerning you are not sure what the scope of the spreadsheet.
Simply put data is downloaded from several sites and entered into the spreadsheet either through a download link(see some problems I have had with not downloading current date data) or keyboard entry("OEX"). Then many moving averages are computed on the data, with selected calculations are displayed with linked formulas to those respective sheets.

That is all mathematic, then "art" is applied, not through formulas by visual inspection, to form trade ideas for market securities.

Again as I have indicated many times, this macro is a time/key board stroke reduced to a minimum. In this case it is one keystroke on the "Calc" spreashsheet

[Doc.AElstein]

Hi
Thanks for the extra info.
I am still not too much the wiser, probably due to my lack generally of Computer projects. As you quite rightly say, I’d need to be an the same room as you for a while to get really clued up. That is outside the scope of a help Forum like this one. But every little helps .- maybe something more sunk in my sub conscious mind to understand the problem. I grasp the general idea I guess.
_.....

Anyways....
Assuming the outstanding problem is still getting Lr for Calc Worksheet. ( By the way, I probably should have used a better variable name for that – I keep confusing myself. StpRw is probably what I would normally use. Sr probably should be something lie SttRow).
( I just edited this post http://www.eileenslounge.com/viewtopic. ... 68#p197668" onclick="window.open(this.href);return false; as I had my Sr and lr mixed up there, sorry about that. )
Anyway the problem is of knowing where to stop adding rows. Or if you are going for my “All Formulas building in Array” solution then the problem is on knowing how big the Range is in row size_...
(rngWs.Rows.Count)
_...which is also how big the first dimension ( pseudo “row” dimension ) in the Array is to be dimensioned to.
ReDim arrrngWs( 1 To rngWs.Rows.Count , 1 To 7 )

So, Regarding or Lr ( or StpRw etc….)

_1) How were you originally intending to do it?
The original File I saw and code in it from this post_..
http://www.eileenslounge.com/viewtopic. ... 44#p197117" onclick="window.open(this.href);return false;
_...From this link ( which is no longer valid )_.....
https://app.box.com/s/3gq9601yj7bb9mpflvqsy942xmqapg6c" onclick="window.open(this.href);return false;
_.......copied one row to the next until no more data was there in one of the columns. With the file I had Sr ( the “start row” ) was hard coded as intStartRow = 3148. All the rows you intended filling in were already filled in and so the code would fill in all existing rows with what was already in them.
So I am thinking the code would never have worked to fill in rows that were not wet filled in? Or have I missed something? I just ask in case you already had an idea of a way to determine how many rows to be filled in, ( for rows not yet filled in).

_2) Dates Column….
I am not sure if you are / were suggesting here
http://www.eileenslounge.com/viewtopic. ... 20#p197154" onclick="window.open(this.href);return false;
that the first column might have llready dates shown. Or you are suggesting that the code should fill dates in all Worksheets up until the current data. If the latter is the case then one solution would be to have at the start of the code a section to add in dates up to the current date. Then in all Worksheet code sections, the Lr would be determined by using the first column in the current Sr calculation.

_ 3) when a link gives zero
I have not had time to work out the full logic behind what is going on.. but:
Based on your Before and After from 18thDec
http://www.eileenslounge.com/viewtopic. ... 00#p197654" onclick="window.open(this.href);return false;

After the code is done for Worksheets “Vix”, “PutCall Ratio” and “OEX” , and Just before the code starts for Worksheets “Calc”, you are at a point where there should be a way to determine where the last row is where you want to fill up to ..
For example: It appears that the final date to stop at is the same . ( at least on the last After that I have from you)

This actual Date can be determined from any of the Worksheets finished.
For example:
Add these code Lines at the end of the code section for “OEX”

Code: Select all

Dim ' Date Value2 for the last row just filled in
Dim LDteVal2 As Double
 Let LDteVal2 = ws.Cells(Lr, 1).Value2 ' .Value 2 date Number for last Filled in row 

Then in the Code section for “Calc” just after where the ws is declared, add these code lines
See Here:
http://www.eileenslounge.com/viewtopic. ... 85#p197985" onclick="window.open(this.href);return false;


This is briefly what the code does…

From the extra bit at the end of the “OEX” section you have the date in .Value2 which appears to be the last day you need in Worksheet “Calc”, LDteVal2
I am using .Value2 as I find it more reliable for doing any sort of comparisons. ( I told you here
http://www.eileenslounge.com/viewtopic. ... 00#p197631" onclick="window.open(this.href);return false;
about what .Value2 is about)

I bring in all the dates in column A in “Calc” into an Array, SttCalcDtsV2(). ( Again I choose to get their .Value2 “values” ) This Array becomes a 2 Dimensional 1 “column” Array.

I want to increase this Array and to put into the newly sized Array the extra dates to take me up to LDteVal2. Unfortunately VBA is a pain in the bum and only lets me increase the size of an Array by its last ( here “column” ) dimension, ( at least if I want to keep all the existing values in it – That is done using the ReDim Preserve , which I use later ) . So I make another Array which is transposed , SrchMtrxT(), and I loop through all the Array values and fill in the transposed Array.

I then Loop While my last Member value in SrchMtrxT() is not yet at LDteVal2. To do this in the loop I increase by one the Array SrchMtrxT() and put in the new member Element the next days date.

Having got the Array needed that includes all the dates required, I re transpose the Array to get an Array, SrchMtrx() which is in the correct orientation to paste into a Worksheet column.

I then paste the new Array, SrchMtrx(), into the Worksheet column A , starting from A2
I have a line ( ‘commented out ) to use Hans Date format correction
http://www.eileenslounge.com/viewtopic. ... 00#p197632" onclick="window.open(this.href);return false;
I did not need it. You may or may not need it, depending on how you have your column A formatted.

I then use the VBA .Match function in a fairly standard way to return me the position of my LDteVal2 in either the Arrays SrchMtrx() or SrchMtrxT(). ( Either Array will do , - they both give me the same results ). Adding 1 to this gives me the row in the Spreadsheet for Lr

I no longer need_..
Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
_..( which in your last code you did not use anyway for “Calc” )

As I now have both Lr and Sr , I use the code line to get the required Range to fill in like this
Set rngWs = Worksheets("Calc").Range("A" & Sr & ":G" & Lr & "")


That’s it!

I tested the code for any amount rows using this file:
https://app.box.com/s/nhrf73dqdq7xbphxw2rp9sf859yfcius" onclick="window.open(this.href);return false;
It appeared to do what I am thinking it should…


( _4 ) Very Minor point.
You asked us for a solution to get dynamically a last Row. – Rudi gave you one here:
http://www.eileenslounge.com/viewtopic. ... 20#p197156" onclick="window.open(this.href);return false;
Just for info: you were actually already using that idea yourself in the original codes:

Code: Select all

 R = Cells(Rows.Count, "D").End(xlUp).Row

_ )


Alan.

[bknight]

Hi
Thanks for the extra info.
I am still not too much the wiser, ...

Anyways....
Assuming the outstanding problem is still getting Lr for Calc Worksheet. ( By the way, I probably should have used a better variable name for that – I keep confusing myself. StpRw is probably what I would normally use. Sr probably should be something lie SttRow).
( I just edited this post http://www.eileenslounge.com/viewtopic. ... 68#p197668" onclick="window.open(this.href);return false; as I had my Sr and lr mixed up there, sorry about that. )
...

_1) How were you originally intending to do it?
...
_.......copied one row to the next until no more data was there in one of the columns. With the file I had Sr ( the “start row” ) was hard coded as intStartRow = 3148. All the rows you intended filling in were already filled in and so the code would fill in all existing rows with what was already in them.
So I am thinking the code would never have worked to fill in rows that were not wet filled in? Or have I missed something? I just ask in case you already had an idea of a way to determine how many rows to be filled in, ( for rows not yet filled in).

The original structure, developed in Office 2K, and had no issues, as it ran for many years. Was as you indicate copy/paste from one row until the next until no data existed, than that loop would end. Control to the next part, do the same routine until all the sheets were filled in. With "Calc" the loop looked two rows ahead for a blank after pasting. The start date was purely arbitrary, 3148 was a previous date and was updated (moved forward in time) periodically.

_2) Dates Column….
...
that the first column might have llready dates shown. Or you are suggesting that the code should fill dates in all Worksheets up until the current data. If the latter is the case then one solution would be to have at the start of the code a section to add in dates up to the current date. Then in all Worksheet code sections, the Lr would be determined by using the first column in the current Sr calculation.

No the dates weren't required to fill in. I copy/paste a new weeks worth on Saturday.

_ 3) when a link gives zero
I have not had time to work out the full logic behind what is going on.. but:
Based on your Before and After from 18thDec
http://www.eileenslounge.com/viewtopic. ... 00#p197654" onclick="window.open(this.href);return false;

I was speaking of the links on the "Calc" sheet resulting in zero data, since it would be pointing to a cell on other sheets that have no data. That is why I asked you to copy and past the last row on 'Calc" to a new row. No data = links resulting in zero data.

After the code is done for Worksheets “Vix”, “PutCall Ratio” and “OEX” , and Just before the code starts for Worksheets “Calc”, you are at a point where there should be a way to determine where the last row is where you want to fill up to ..
For example: It appears that the final date to stop at is the same . ( at least on the last After that I have from you)

That was rather much like I was thinking i.e. use the last calculated date to set up a ubound to the data to be filled in for "Calc" I'll look at you code below sometime tomorrow, but I think that would be the way to limit rows being filled out on "Calc".

This actual Date can be determined from any of the Worksheets finished.
For example:
Add these code Lines at the end of the code section for “OEX”

Code: Select all

Dim ' Date Value2 for the last row just filled in
Dim LDteVal2 As Double
 Let LDteVal2 = ws.Cells(Lr, 1).Value2 ' .Value 2 date Number for last Filled in row 

...

about what .Value2 is about)

All the finishing dates are identical on all the sheets.

I bring in all the dates in column A in “Calc” into an Array, SttCalcDtsV2(). ( Again I choose to get their .Value2 “values” ) This Array becomes a 2 Dimensional 1 “column” Array.

I want to increase this Array and to put into the newly sized Array the extra dates to take me up to LDteVal2. Unfortunately VBA is a pain in the bum and only lets me increase the size of an Array by its last ( here “column” ) dimension, ( at least if I want to keep all the existing values in it – That is done using the ReDim Preserve , which I use later ) . So I make another Array which is transposed , SrchMtrxT(), and I loop through all the Array values and fill in the transposed Array.

I then Loop While my last Member value in SrchMtrxT() is not yet at LDteVal2. To do this in the loop I increase by one the Array SrchMtrxT() and put in the new member Element the next days date.

Having got the Array needed that includes all the dates required, I re transpose the Array to get an Array, SrchMtrx() which is in the correct orientation to paste into a Worksheet column.

I then paste the new Array, SrchMtrx(), into the Worksheet column A , starting from A2
I have a line ( ‘commented out ) to use Hans Date format correction
http://www.eileenslounge.com/viewtopic. ... 00#p197632" onclick="window.open(this.href);return false;
I did not need it. You may or may not need it, depending on how you have your column A formatted.

I then use the VBA .Match function in a fairly standard way to return me the position of my LDteVal2 in either the Arrays SrchMtrx() or SrchMtrxT(). ( Either Array will do , - they both give me the same results ). Adding 1 to this gives me the row in the Spreadsheet for Lr

I no longer need_..
Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
_..( which in your last code you did not use anyway for “Calc” )

I don't understand "_..( which in your last code you did not use anyway for “Calc” ",
both Lr and Sr were set

As I now have both Lr and Sr , I use the code line to get the required Range to fill in like this
Set rngWs = Worksheets("Calc").Range("A" & Sr & ":G" & Lr & "")


That’s it!

I tested the code for any amount rows using this file:
https://app.box.com/s/nhrf73dqdq7xbphxw2rp9sf859yfcius" onclick="window.open(this.href);return false;
It appeared to do what I am thinking it should…


( _4 ) Very Minor point.
You asked us for a solution to get dynamically a last Row. – Rudi gave you one here:
http://www.eileenslounge.com/viewtopic. ... 20#p197156" onclick="window.open(this.href);return false;
Just for info: you were actually already using that idea yourself in the original codes:

Code: Select all

 R = Cells(Rows.Count, "D").End(xlUp).Row

_ )


Alan.

The use of R = Cells(Rows.Count, "D").End(xlUp).Row[/code] _ )
was to extend a named range every time a row was added. This code was finalized perhaps 10 years ago, and ran until I "upgraded" to Office 2007 without issues. Since it ran, I hadn't looked at the code simple as it was for a long time. I forgot that those range finding codes were in there, when I asked Rudi. So yes I forgot. In a year I will probably forget all these hard earned lessons.

[Doc.AElstein]

Hi..

_1) I have missed something here: Your code was copying and pasting data, Yes. And Yes it stopped when no data was in the next row. The data that was there in the file I had was exactly the same as the data it was putting in. Possibly you are saying that the existing data was in a real file different. The file I had looked exactly the same before and after as it was replacing the data in rows with the same data. Looking again I think possibly you were looping until no data was in the next row in column B
Loop Until Cells(I, 2) = ""
Hence our use of B in
Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
I think the confusion is that the file I had had data in all columns. The later befores had data in column B and a few others, but the columns to be filled in ere empty. I guess the first File was in error – it was possibly an After with all rows filled in. So I think I understand now for the first three Worksheets how you determined when to stop filling in.

It is still not clear to me how you got the Last row for “Calc”. I do not see why
Loop Until Cells(I + 1, 2) = ""
Worked. But if it did then it suggests this would work
Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1
But I am possibly missing something again. Possibly I do not have a before which would have worked with your very original codes.
The Befores I have seen would not work to give the Afters with the codes we developed in this Thread, as we could not determine the required Lr…hence….the outstanding problem to find a way to get Lr for “Calc”

Following directly on from that
_.......

Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
_..( which in your last code you did not use anyway for “Calc” )….
I don't understand "_..( which in your last code you did not use anyway for “Calc” ",

In the code you did get to “work” and presented here:_...
http://www.eileenslounge.com/viewtopic. ... 00#p197651" onclick="window.open(this.href);return false;
_...the code line_..
Let Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
_... is present in the “Calc” code section. But you do not use it. In the code line that uses it in the other 3 Worksheet code sections, you replaced Lr with Sr ( in the "Calc" code section ) , thus
Set rngWs = Worksheets("Calc").Range("A" & Sr & ":G" & Sr & "")
This means the Range Object of the Range to be filled in would always be a single row, that is to say these 7 Cells:
"A" & Sr and "B" & Sr and "C" & Sr and "D" & Sr and "E" & Sr and "F" & Sr and "G" & Sr
So for “Calc” you only ever fill in one row cells A – G for that code version of yours.


It is difficult for me to see how
Loop Until Cells(I + 1, 2) = ""
Was able to determine Lr, ( that is to say the last row to be filled in ) as in all the Befores I have seen there was never anything in column B. The exception being the very first File – But in which case Loop Until Cells(I + 1, 2) = "" would have resulted in you stopping the copy paste action at one row short of the last already filled in row.

So the logic still escapes me there. ( You gave some Files privately to Hans I believe. Possibly not seeing those has lead to some confusion. The File you uploaded for me is no longer available at the Link you gave This is that file you gave me originally: https://app.box.com/s/rfj5fulrzdfxfgxv3i64apm5lz5lhkjq" onclick="window.open(this.href);return false; )


Alan.

P.S. The full code from which the new snippet comes which I described in my last post is in the File I uploaded in that post. The code is :
Sub CopyCellsFormulasTest_20Dec()
Here the File again to test that on.
https://app.box.com/s/nhrf73dqdq7xbphxw2rp9sf859yfcius" onclick="window.open(this.href);return false;

[bknight]

That is correct the files on the upload site have been changed to reflect the new data, so the original files you down loaded will not be there, sorry. However the file will have the same basic structure date in column 1 on all the sheets data exists in column 1 through x on the rest depending on which sheet you are viewing. This is what three of the sheets look like after updating last night. I'll post the last image in another post.

[bknight]

This is the "Calc" sheet after updating last night.
As to your question how the last part of the macro only copied one row, the way the loop works with the copy copies one line EVEN if more than one day data existed, so I would have to manually copy/paste how many rows were missed, but that really never happened as I update this every night Mon. through Fri.

[Doc.AElstein]

Hi..
Thanks for the screenshot. ( I only just noticed if I click on them I get a bigger shot and can clearly see the dates etc. – so I see what you mean and that you have it updated for yesterday ). You will appreciate I am not too clear of your final intentions and requirements.
For Adding a single row every day , then the last code you showed will do that. Probably a much simple code just set to add a row would do that also.
Again I am not getting the picture clear. I am not sure exactly what code you are using for what.

Possibly you are saying your original codes only ever added one line to “Calc”. Whether that is wanted or not I am not sure in the meantime.

No matter.
I think you realize the code ( codes ) you have is flexible for 1 or more rows in the first 3 Worksheets. For calc they will only add 1 row.
If you wanted the code to add dates and rows of more than one to calc to bring the date up to date along with the other Worksheets then I have shown you with my last code one way to do that.

I expect you have all the information you need now in the Thread to achieve your final goal
:)

Alan

[bknight]

Hi..
...
Possibly you are saying your original codes only ever added one line to “Calc”. Whether that is wanted or not I am not sure in the meantime.

No matter.
I think you realize the code ( codes ) you have is flexible for 1 or more rows in the first 3 Worksheets. For calc they will only add 1 row.
If you wanted the code to add dates and rows of more than one to calc to bring the date up to date along with the other Worksheets then I have shown you with my last code one way to do that.

...
:)

Alan

Thanks for your time and effort. Could you post the code you have for more than one line in a single post, please?

[Doc.AElstein]

Hi..
I’m afraid it is quite a bit too big – but did you see that I said it is in the File I uploaded?
At the bottom of this Post
http://www.eileenslounge.com/viewtopic. ... 20#p198020" onclick="window.open(this.href);return false;

If you need it in the Thread then let me know and I will try to chop a few bits off

Alan

[bknight]

Umm, I overlooked that piece of the post.
There 7 macros:
Calculate9and21DayMovingAverage-- Mine from original formulas.
CopyCellFormulas
CopyCellFormujlasTest
CopyCellFormulasTest_18DecVix
CopyCellFormulasTest_20Dec
This Workbook.ApplicationRangeObject--Probably your looking at Named ranges extension part of code.

So which one of the other 4 has the more general case for "Calc"?

[Doc.AElstein]

Hi..
Hmm ….

I really think you should take some time to read what I write.. I know there is a lot.. but I am repeating myself again :(

P.S. The full code from which the new snippet comes which I described in my last post is in the File I uploaded in that post. The code is :
Sub CopyCellsFormulasTest_20Dec()
Here the File again to test that on.
https://app.box.com/s/nhrf73dqdq7xbphxw2rp9sf859yfcius" onclick="window.open(this.href);return false;

:(

[bknight]

Thanks for facilitating my laziness.

[bknight]

Looking at the sheet "Vix" fourmulas are incorrect, but that is my fault as I kept changing the formulas Row 3267 should be
F3267=(F3266*$I$3)+(E3267*$I$2)
G3267=G3266*$I$7+E3267*$I$6
H3267=F3267/G3267
I3267=AVERAGE(E3218:E3267)

note that

Code: Select all

Set rngWs = Worksheets("PutCall Ratio").Range("K" & Sr & ":L" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 2) ' We must use Redim as this allows us to use the Rows Count Property
    For Cntrw = 1 To (UBound(arrrngWs(), 1) - 0) ' at every "row" in the Array, (Up to and including the last),  we ...
    ' ... Put in each string Formula
     Let arrrngWs(Cntrw, 1) = "=AVERAGE(E" & Sr - 9 + Cntrw & ":E" & Sr - 2 + Cntrw & ")"
     Let arrrngWs(Cntrw, 2) = "=AVERAGE(E" & Sr - 21 + Cntrw & ":E" & Sr - 2 + Cntrw & ")"
    Next Cntrw
Let rngWs.Value = arrrngWs() ' This will paste out the Formula values to the Worksheet
'Set rngWs = Worksheets("PutCall Ratio").Range("K" & Sr & ":L" & Lr & "")
rngWs.NumberFormat = "0.00"
Cells(Lr + 1, 2).Select

Set rngWs = Worksheets("PutCall Ratio").Range("F" & Sr & ":H" & Lr & "")
Was added after the first "PutCall Ratio" part
Other than that the code appears at first look to work exactly as desired, just as you posted.
I can not remember which of your posts that you indicated it works, but it does and I will change it today.
Thanks you very much for the time and effort you put into helping me with a code that does not bog down as my copy/paste tended to do.

EDITED for readability.

[bknight]

Alan, I have observed some strange behaviors:
Before: https://app.box.com/s/r8zvw43s3aku8i30vp8plftgxro3fnrr" onclick="window.open(this.href);return false;
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)
After: https://app.box.com/s/0pc52dzfoi0g0700tlzwjxrvcq3fh3ab" onclick="window.open(this.href);return false;

Ran the Calculate All.

Note all dates have been filled out in the "Calc" sheet; zeros for everything but the date, same date see above
Look at the date column in the "Calc" sheet prior to 1/3/2017. All up to an including Row 2 have been changed to values, not formulas!

I believed that the routine only added rows to the "Calc" sheet until a date had no data on "OEX", why the fill in?
Why all the dates prior to last working date(1/3/2017) were changed to values?

Code in the next post.

[bknight]

Code: Select all

Sub CopyCellsFormulas()
Dim dteDateValue As Date
Dim I As Integer, J As Integer, intStartRow As Integer, R As Integer
Dim Lr As Long, Sr As Long, Cntrw As Long
Dim dbl9Value As Double, dbl21Value As Double
Dim Temp() As Variant
Dim rngWs As Range
Dim arrrngWs() As Variant
Dim ws As Worksheet
Const defName As String = "DataCol"
Const defNameOEX_High As String = "OEX_High"
Const defNameOEX_Low As String = "OEX_Low"
Const defNameDATE_Range As String = "DATE_Range"

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
' Vix
 Set ws = Worksheets("Vix")
 Worksheets("Vix").Activate
 ws.Activate
 Let Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
 Let Sr = (ws.Cells(ws.Rows.Count, "F").End(xlUp).Row) + 1
 Let Temp() = Worksheets("Vix").Range("F" & (Sr - 1) & ":I" & (Sr - 1) & "").Formula
 Let Temp() = Worksheets("Vix").Range("F" & Sr - 1 & ":I" & Sr - 1 & "").Formula
 'Debug.Print Temp(1, 1) ' =(F3258*$I$3)+(E3259*$I$2)
 'Debug.Print Temp(1, 2) ' =G3258*$I$7+E3259*$I$6
 'Debug.Print Temp(1, 3) ' =F3259/G3259
 'Debug.Print Temp(1, 4) ' =AVERAGE(E3210:E3259)
 Set rngWs = Worksheets("Vix").Range("F" & Sr & ":I" & Lr & "")
 ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 4)
    For Cntrw = 1 To (UBound(arrrngWs(), 1) - 0)
     Let arrrngWs(Cntrw, 1) = "=(F" & Sr - 2 + Cntrw & "*$I$3)+(E" & Sr - 1 + Cntrw & "*$I$2)" '
     Let arrrngWs(Cntrw, 2) = "=G" & Sr - 2 + Cntrw & "*$I$7+E" & Sr - 1 + Cntrw & "*$I$6"     '
     Let arrrngWs(Cntrw, 3) = "=F" & Sr - 1 + Cntrw & "/G" & Sr - 1 + Cntrw & ""             '
     Let arrrngWs(Cntrw, 4) = "=AVERAGE(E" & Sr - 50 + Cntrw & ":E" & Sr - 1 + Cntrw & ")"   '
    Next Cntrw
 Let rngWs.Value = arrrngWs()
Cells(Lr + 1, 2).Select
' PutCall Ratio
Set ws = Worksheets("PutCall Ratio")
Worksheets("PutCall Ratio").Activate
Let Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
Let Sr = (ws.Cells(ws.Rows.Count, "F").End(xlUp).Row) + 1
Set rngWs = Worksheets("PutCall Ratio").Range("F" & Sr & ":H" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 3)
    For Cntrw = 1 To (UBound(arrrngWs(), 1) - 0)
     Let arrrngWs(Cntrw, 1) = "=(F" & Sr - 2 + Cntrw & "*$I$3)+(E" & Sr - 1 + Cntrw & "*$I$2)" '
     Let arrrngWs(Cntrw, 2) = "=(G" & Sr - 2 + Cntrw & "*$I$7)+(E" & Sr - 1 + Cntrw & "*$I$6)"
     Let arrrngWs(Cntrw, 3) = "=F" & Sr - 1 + Cntrw & "/G" & Sr - 1 + Cntrw & ""             '
    Next Cntrw
Let rngWs.Value = arrrngWs()

Set rngWs = Worksheets("PutCall Ratio").Range("K" & Sr & ":L" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 2) 
    For Cntrw = 1 To (UBound(arrrngWs(), 1) - 0) 
     Let arrrngWs(Cntrw, 1) = "=AVERAGE(E" & Sr - 9 + Cntrw & ":E" & Sr - 2 + Cntrw & ")"
     Let arrrngWs(Cntrw, 2) = "=AVERAGE(E" & Sr - 21 + Cntrw & ":E" & Sr - 2 + Cntrw & ")"
    Next Cntrw
Let rngWs.Value = arrrngWs() ' This will paste out the Formula values to the Worksheet
'Set rngWs = Worksheets("PutCall Ratio").Range("K" & Sr & ":L" & Lr & "")
rngWs.NumberFormat = "0.00"
Cells(Lr + 1, 2).Select

'OEX
Set ws = Worksheets("OEX")
Worksheets("OEX").Activate
Let Lr = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
Let Sr = (ws.Cells(ws.Rows.Count, "H").End(xlUp).Row) + 1
Set rngWs = Worksheets("OEX").Range("H" & Sr & ":H" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 1)
    For Cntrw = 1 To (UBound(arrrngWs(), 1) - 0)
     Let arrrngWs(Cntrw, 1) = "=(H" & Sr - 2 + Cntrw & "* $I$4) + (F" & Sr - 1 + Cntrw & "* $i$3)"
    Next Cntrw
Let rngWs.Value = arrrngWs() 
Cells(Lr + 1, 3).Select
Dim LDteVal2 As Double
 Let LDteVal2 = ws.Cells(Lr, 1).Value2 ' .Value 2 date Number for last Filled in row
Set ws = Worksheets("Calc")
Dim SttCalcDtsV2() As Variant ' For "capture" of range of dates in column 1
 Let SttCalcDtsV2() = ws.Range("A2:A" & ws.Cells(Rows.Count, 1).End(xlUp).Row).Value2
Dim SrchMtrxT() As Double: ReDim SrchMtrxT(1 To 1, 1 To UBound(SttCalcDtsV2(), 1))
    For Cntrw = 1 To UBound(SttCalcDtsV2(), 1) 
     Let SrchMtrxT(1, Cntrw) = SttCalcDtsV2(Cntrw, 1) 
    Next Cntrw
    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
 Dim SrchMtrx() As Double
 ReDim SrchMtrx(1 To UBound(SrchMtrxT(), 2), 1 To 1) 
    For Cntrw = 1 To UBound(SrchMtrxT(), 2)
     Let SrchMtrx(Cntrw, 1) = SrchMtrxT(1, Cntrw) 
    Next Cntrw
 Let ws.Range("A2").Resize(UBound(SrchMtrx(), 1), 1).Value2 = SrchMtrx() ' The Range Object of A2 resized to 1st dimension ("row") of SrchMtrx() has its Value2#s given to Value2's of SrchMtrx()
 Let Lr = Application.Match(LDteVal2, SrchMtrxT(), 0) + 1 'position "along" of ( LDteVal2  , in  SrchMtrxT() , stipulating an exact Match  )
 Let Lr = Application.Match(LDteVal2, SrchMtrx(), 0) + 1 'position "down" of ( ..... etc..
Worksheets("Calc").Activate
Let Sr = (ws.Cells(ws.Rows.Count, "C").End(xlUp).Row) + 1
'Calc
Set rngWs = Worksheets("Calc").Range("A" & Sr & ":G" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 7)
    For Cntrw = 1 To (UBound(arrrngWs(), 1) - 0)
        Let arrrngWs(Cntrw, 1) = "='PutCall Ratio'" & "!A" & Sr + 130 + Cntrw
        Let arrrngWs(Cntrw, 2) = "=((C" & Sr - 1 + Cntrw & "+D" & Sr - 1 + Cntrw & ")/2)*100"
        Let arrrngWs(Cntrw, 3) = "='PutCall Ratio'" & "!H" & Sr + 130 + Cntrw
        Let arrrngWs(Cntrw, 4) = "=Vix!H" & Sr + 79 + Cntrw
        Let arrrngWs(Cntrw, 5) = "=OEX!A" & Sr + 366 + Cntrw
        Let arrrngWs(Cntrw, 6) = "=OEX!F" & Sr + 366 + Cntrw
        Let arrrngWs(Cntrw, 7) = "=OEX!H" & Sr + 366 + Cntrw
    Next Cntrw
Let rngWs.Value = arrrngWs() ' This will paste out the Formula values to the Worksheet
Cells(Lr, 1).Select
Selection.NumberFormat = "mm/dd/yy;@"
Cells(Lr, 5).Select
Selection.NumberFormat = "mm/dd/yyyy;@"
Cells(Sr, 1).Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub 'CopyCellsFormulas()

[Doc.AElstein]

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