Macro not working as expected

[bknight]

Apparently there is no worksheet named Vix in the workbook you're testing the code in.

Not true, you have the workbooks.
However there are a number of workbooks open and perhaps the "wrong" workbook is in focus.

[Doc.AElstein]

What do you guys think?

I need a bit of time to think. I am getting a bit confused.. let me re read your last few posts !!

[Doc.AElstein]

Apparently there is no worksheet named Vix in the workbook you're testing the code in.

Not true, you have the workbooks.
However there are a number of workbooks open and perhaps the "wrong" workbook is in focus.

If your code is in the workbook with the Vix Worksheet in it them use this
Set ws = ThisWorkbook.Worksheets("Vix")

[Doc.AElstein]

Or use the Workbooks Collection Object of open Workbooks to referrence in this sort of way
Set ws = Workbooks("VIXData050824After.xls").Worksheets("Vix")

[bknight]

The method of using two columns G and H works (code wise) Not sure it won't change the value in the cell, but if that is the case I can deal with that too.

[Doc.AElstein]

...Alan's procedure is to find the formulas to "copy" to the next empty rows.

No it is not.

I just use for convenience, ( personal preference ) the Immediate window to get the formulas in the sort of form I want. It just makes sure i do not type the formulas in wrong as I often make typos.

After I have done that I do not need any of the Temp() and Debug.Print stuff
Or as I mentioned before you can trash all that Temp() and Debug.Print stuffand just copy from the formula bar from some arbritrary row that has the formulas in, or just write the formulas fromm the top of your head. I am sure you know them all off by heart



My code loops as many times as there are rows that I have in the range to have formulas put in it.
But it does not loop through any rows and does not do any copying to the worksheet.
It builds a large Array full of all the formulas I need.
After the looping is finished it pastes all the formulas out in one go

I expect you may know that, as you said “copy” and not copy, but just clarifying for Hans or anyone else

[Doc.AElstein]

.... there are a number of workbooks open and perhaps the "wrong" workbook is in focus.

What you are focusing on ( Looking at ) ( Having Active ) is the active Workbook.
Set ws = Worksheets("Vix") will go to the Active Workbook. So, yes, that could be your problem !

[Doc.AElstein]

I am sure that you know this, but just to clarify again for anyone else…
In my code idea/ snippet, The formula is built one “row” at a time. I say “row” and not row. It is actually building one line in an Array. For a 2 Dimensional Array you can “think” of its first Dimension as a “row”. ( Similarly you can ”think” of the second dimension as a “column” .
So you could “think” of it as

Arr( “row , “column” )

But do not get confused and think you are doing anything at all to the Worksheet row or column . The Array is held in some obscure place in computer memory, ( god knows where, I bet the original programmers forgot in the meantime )

If you want to be pedantic then it is all visual virtual crap anyway and so a Worksheet is also not really like a Sheet of paper that you are working on. But at least it looks like it on your screen. Interacting with a worksheet takes a lot of doing. Probably that is because a cell is a Range Object with lots of possible things, like values, colors, size etc.. etc…There are so many, that I expect no one person knows them all in the meantime ( well may be Hans & co. do ).
As you saw with your original codes, you had time / memory problems etc. This war because you were continually interacting with the worksheet at each row. My codes do not . They only interact with the worksheet a few times For example this_...
Let rngVix.Value = arrrngVix()
_.. probably takes about the same time as one of your row copy / paste actions from your early codes. But my code line there puts all the rows values ( string formulas ) in. The copy / paste action just puts one in. hence you see the time improvement…..

Interacting with a simple Array of values “” internally in code” ( string formulas in this case ) is relatively speaking much simpler to do. “Values” ( which includes .Value, .Value2, .Formula, .FormulaR1C1 etc etc.. ) are much simpler to handle than everything that you can have in a Range Object.

A Range Object is frighteningly large if you look at that - you cannot look at it in the Immediate Window as the Immediate Window that just shows values ( That is why each Debug.Print is for each value. – That I think you now see is either one Element of an Array, so like
Debug.Print Arr(1, 1) etc..
or if the variable is not an array then just print out that variable, so like


An alternative to see all the Arrays elements in one go or an entire Range Object you do the following:
_ stop your code at some point after you have filled ( Set )a Range Object like, rngVix, then
_Hit F9
_Select something like Add
Then the Watch Window ( Überwachungsausdrücke in German ) should come up with
+ rngVix in it
_ Keep hitting the + ‘s ……see how many things are there !!!

rngVix.jpg http://imgur.com/LExlPwN" onclick="window.open(this.href);return false;

rngVix.JPG

( a disadvantage of the Watch Window ( Überwachungsausdrücke ) is that you cannot copy from it )

[Doc.AElstein]

I just thought I would check what I said here….
http://www.eileenslounge.com/viewtopic. ... 60#p197499" onclick="window.open(this.href);return false;

Here you go..

Changed my code snippet to have a String variable for a single Cell Formula
( The code needs this file Open to run
“VIXData050824After.xls”
https://app.box.com/s/vfevzf8sfm0pnyftdqo8bnz7ywcwct79" onclick="window.open(this.href);return false; )

Code: Select all

Sub CopyCellsFormulas() '  http://www.eileenslounge.com/viewtopic.php?f=27&t=25344&start=60#p197499
    Dim dteDateValue As Date
    Dim I As Integer, J As Integer, intStartRow As Integer, R As Integer
    Dim dbl9Value As Double, dbl21Value As Double
    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 = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    'New Data Aquisitions Begins 10/21/2003 Put/Call
    '01/02/2004 Row 53
    '07/01/2004 Row 177
    '01/03/2005 Row 305
    '07/01/2005 Row 430
    '01/03/2006 Row 557
    '07/03/2006 Row 682
    '01/02/2009 Row 1312
    '01/03/2012 Row 2067
    '07/02/2012 Row 2142
    '01/02/2013 Row 2267
    '07/01/2013 Row 2391
    '01/02/2014 Row 2519
    '07/01/2015 Row 3148
    '07/01/2016 Row 3199
    'intStartRow = 3199
    'I = intStartRow
Dim ws As Worksheet: Set ws = Worksheets("Vix"): Set ws = ThisWorkbook.Worksheets("Vix")
Set ws = Workbooks("VIXData050824After.xls").Worksheets("Vix")
Worksheets("Vix").Activate
Dim Lr As Long: Let Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
Dim Sr As Long: Let Sr = (ws.Cells(ws.Rows.Count, "F").End(xlUp).Row) + 1
    'Do
        'I = I + 1
        'Range(Cells(I - 2, 6), Cells(I - 2, 9)).Copy Range(Cells(I - 1, 6), Cells(I - 1, 9))
    'Loop Until Cells(I, 2) = ""
Rem Code section to help get Formulas: Put stop in left margin at Set rngVix = ..  then run code and Hit Ctrl+G to display Immediate Window
Dim Temp() As Variant: Let Temp() = ws.Range("F" & (Sr - 1) & ":I" & (Sr - 1) & "").Formula
Dim strTemp As String: Let strTemp = ws.Range("F" & (Sr - 1) & ":F" & (Sr - 1) & "").Formula
Let strTemp = ws.Range("F" & (Sr - 1) & "").Formula
Debug.Print strTemp    ' =(F3261*$I$3)+(E3262*$I$2)
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)

Dim rngVix As Range ' variable to hold the entire Range you are currently considering to add formulers to
Set rngVix = Worksheets("Vix").Range("F" & Sr & ":I" & Lr & "")
Dim arrrngVix() As Variant ' We know the size of the Array, but must use Re Dim below as Dim only takes Numbers. We must use Variant as otherwise the final Paste Out to the Worksheet for Formula strings does not always work. http://www.mrexcel.com/forum/excel-questions/887822-formula-link-cell-array-instead-cell-value-based-cell-r-c-co-ordinates-2.html
ReDim arrrngVix(1 To rngVix.Rows.Count, 1 To 4) ' We must use Redim as this allows us to use the Rows Count Property
Dim Cntrw As Long
    For Cntrw = 1 To (UBound(arrrngVix(), 1) - 0) ' at every "row" in the Array, (Up to and including the last),  we ...
    ' ... Put in each string Formula
     Let arrrngVix(Cntrw, 1) = "=(F" & Sr - 2 + Cntrw & "*$I$3)+(E" & Sr - 1 + Cntrw & "*$I$2)" '
     Let arrrngVix(Cntrw, 2) = "=G" & Sr - 2 + Cntrw & "*$I$7+E" & Sr - 1 + Cntrw & "*$I$6"     '
     Let arrrngVix(Cntrw, 3) = "=F" & Sr - 1 + Cntrw & "/G" & Sr - 1 + Cntrw & ""             '
     Let arrrngVix(Cntrw, 4) = "=AVERAGE(E" & Sr - 50 + Cntrw & ":E" & Sr - 1 + Cntrw & ")"   '
    Next Cntrw
Let rngVix.Value = arrrngVix() ' This will paste out the Formula values to the Worksheet
End Sub

You see this now:
( dont be confused with the 3261 0r 3258 differences in the code or the screenshot below -- - - I think you get the point that those from the Debug.Print window are just to get the general formula format - The actual row numbers used are dynamically assigned by the Sr - 2 + Cntrw type stuff when the actual used formulas are built )

strTempAndarrTemp.JPG http://imgur.com/PEIWk2E" onclick="window.open(this.href);return false;
http://imgur.com/PEIWk2E" onclick="window.open(this.href);return false;

[bknight]

OK, you have mail.

[Doc.AElstein]

Hi

OK, you have mail.

OK, as I said I will try to get back to you in a day or so... ( get me some files ( Before and After ) in the meantime so I do not get confused where we are with everything )

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

_.. just a last thought while I am here,as you probably should not get into my bad habits…....

1) Change all occurrences of Temp() to Temp
2) After the 3rd Let Temp = ..., refer to Temp instead of to Temp(1, 1).

For Debugging , Debug.Printing and the such that is probably the best ( or proper way )
The idea is this..
with_...
Dim Temp() as Variant.
_...you are restricting yourself to an Array of Variant Types
I was expecting to get a “row” or field of formulas in Variant type Member Elements to be “chucked” at my Temp()

As a general rule I do tend to Dim a variable used in debugging as like
Dim vTemp As Variant
or
Dim vTemp ‘ defaults to Dim vTemp as Variant

Declared ( Dim’ ed ) like that it can still “take” or “hold” an Array of Variant types or an Array of any types or even anything other than an Array, such as a String .. For debugging that is useful as you may not know what might be chucked at it. For example, in some cases the same code line may chuck something different back in different runs of it, - like a number if a formula "works" or an error if the formula doesn't work )

_.....

….
2) After the 3rd Let Temp = ..., refer to Temp instead of to Temp(1, 1).

When you know it has an Array of values in it then to get a single value ( as necessary, for example, for the Debug.Print line ) , then you still need to get at each value like
vTemp( 1, 1 ) , vTemp ( 1 , 2 ) etc…..

On the other hand, when you know it has a single value ( like a single formula String ) in it, then you need to get at that value like
vTemp

Hence Hans meant like still for say a three cell row code section , where vTemp had a 3 Element Array chucked at it you do:

Debug.Print vTemp(1, 1)
Debug.Print vTemp(1, 2)
Debug.Print vTemp(1, 3)

but then if the next section if you are just looking at a single column cell formula, ( I mean looking at a single Cell ) then do

Debug.Print vTemp

In general in VBA the () tells or prepares VBA to take / expect an Array. There are times when that can be very important – For example, in some cases it prevents VBA getting confused and looking for an optional argument instead )
I prefer to be as Explicit as possible. Just an another annoying personal preference
:)´

[bknight]

The whole code, much thanks to Alan, Hans, and Rudi. Not tested but will be shortly, please submit any changes/alterations.

Code: Select all

Sub CopyCellsFormulasTest()
    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"
Set ws = Worksheets("Vix")
Worksheets("Vix").Activate
Let Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
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)-->This debug print on all sections to aid in code development, but will be deleted in the remaining three pieces
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) = "=SUM(E" & Sr - 4 + Cntrw & ":E" & Sr - 1 + Cntrw & ")/4" '
     Let arrrngWs(Cntrw, 2) = "=SUM(E" & Sr - 81 + Cntrw & ":E" & Sr - 1 + Cntrw & ")/81"
     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() 

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) = "=SUM(E" & Sr - 81 + Cntrw & ":E" & Sr - 1 + Cntrw & ")/81" '
     Let arrrngWs(Cntrw, 3) = "=F" & Sr - 1 + Cntrw & "/G" & Sr - 1 + Cntrw & ""             '
    Next Cntrw
Let rngWs.Value = arrrngWs()

    I = Lr - 23
    Do
        dbl9Value = 0: dbl21Value = 0
        For J = I To (I - 20) Step -1
            dbl21Value = Cells(J, 5) + dbl21Value
        Next J
        Cells(I, 12) = dbl21Value / 21
        For J = I To (I - 8) Step -1
            dbl9Value = Cells(J, 5) + dbl9Value
        Next J
        Cells(I, 11) = dbl9Value / 9
        Cells(I, 11).NumberFormat = "0.00": Cells(I, 12).NumberFormat = "0.00"
        I = I + 1
    Loop Until Cells(I, 1) > Now() Or Cells(I, 2) = ""

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, "G").End(xlUp).Row) + 1
Rem Code section to help get Formulas: Put stop in left margin at Set rngVix = ..  then run code and Hit Ctrl+G to display Immediate Window
Set rngWs = Worksheets("OEX").Range("G" & Sr & ":H" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 2)
    For Cntrw = 1 To (UBound(arrrngWs(), 1) - 0)
    Let arrrngWs(Cntrw, 2) = "=SUM(F" & Sr - 161 + Cntrw & ":F" & Sr - 2 + Cntrw & ")/160"
    Next Cntrw
Let rngWs.Value = arrrngWs() ' This will paste out the Formula values to the Worksheet

Set ws = Worksheets("Calc")
Worksheets("Calc").Activate
Let Lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
Let Sr = (ws.Cells(ws.Rows.Count, "C").End(xlUp).Row) + 1

Set rngWs = Worksheets("Calc").Range("A" & Sr & ":H" & 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
End Sub

Start File
https://app.box.com/s/r8zvw43s3aku8i30vp8plftgxro3fnrr" onclick="window.open(this.href);return false;
I'll add the after later with an edit, I'm off to a party.

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

[Doc.AElstein]

Hi

Just some quick very minor initial points, - ( I have not had time to look in too much detail fully yet: )

_(i) Avoid using the pointed brackets ( > < ) in ‘comments ( and codes ) as much as possible. You may have to use them in codes – If so, fair enough, - do it.
I just mention it in passing as although I have not had a problem here at Eileen’s Lounge yet, I do find that posting any code generally around the internet can sometimes causes problems when a pointy bracket is mistaken as a “HTML Code Tag thingy”
( _..... Sometimes in a code there is an alternative. For example: the last two lines here are the same

Code: Select all

Sub PointedTesties()
 Dim Lr As Long
   If Lr <> 2 Then MsgBox prompt:="""Lr"" is Not equal to 2, " & vbCrLf & "It should be a ""empty"" Long which is zero,  like this  " & Lr & ""
   If Not (Lr = 2) Then MsgBox prompt:="""Lr"" is Not equal to 2, " & vbCrLf & "It should be a ""empty"" Long which is zero,  like this  " & Lr & ""
End Sub

_....)

_ (ii) Avoid the extra brackets like in (Lr = 2) above. It is a bad habit of mine which in some situations may catch you out as it may do something you did not want.
I do often use extra brackets. Just a personal preference. A bad Habit. I have them. :)
One reason for having a syntax in a code ( or an order of mathematical operations ) is to avoid a periphery of brackets.
This code line_...

Code: Select all

 Let Temp() = Worksheets("Vix").Range("F" & (Sr - 1) & ":I" & (Sr - 1) & "").Formula

_...would be better, probably, as this

Code: Select all

 Let Temp() = Worksheets("Vix").Range("F" & Sr - 1 & ":I" & Sr - 1 & "").Formula

I like a periphery of brackets and the such. I do not know why. Maybe I am something of a anarchist programmer. I guess VBA is a anarchistic programming language actually. So it suits ? ( VBA ( so experts tell me ) rejects sometimes the Object Orientated Programming language hierarchy, which we are continually told it adheres to )

_(iii) It is very good that you are doing tests on a reduced range, but use also a few rows rather than one, just to make sure things like loops get the correct number of loops.
Having said that, checking something that is intended to work over “many” things is always good to check for both the case if maybe no things are to be done or , importantly , check for the case of there being just 1 thing to be done. --- As you have seen in the Temp(), Temp, vTemp discussions we had - there are often cases in VBA when different things happen for a single case as opposed to the “more than 1 case”

_(iv) In your code from the last post you forgot your Sr calculation code line for Vix worksheet

Code: Select all

Sr = (ws.Cells(ws.Rows.Count, "F").End(xlUp).Row) + 1

_(v) As I mentioned a few times, I use Let. Most people do not. It annoys many when you do that. Just be aware of that!

_(vi) Possibly you have already got the point about this one:
Make good use of the Watch Window ( Überwachungsausdrücke in German ) and Immediate Window ( Direktbereich in German ) . It really helps in development. It is particularly useful if you have a second Monitor, ( Use the extra Monitor in extended monitor mode, not Duplicated screen mode ). I use a very big old Television for my second monitor. That gives you plenty of room for all the extra windows.

_(vii) This is a bit to justify my use of the Debug.Print Window for getting my formulas:
In my German Excel the formulas come in the worksheet ( or rather in the Formula bar ) in German_….
Here the code has just Debug.Printed the Temp(1, 1) in the Immediate ( Direktbereich ) Window. ( The Temp() Array was already filled with Temp() = Worksheets("Vix").Range("F" & (Sr - 1) & ":I" & (Sr - 1) & "").Formula , so that is complete filled to see in the Watch ( Überwachungsausdrücke ) Window). In the Formula bar I see the formula in German Format.
ViewAllTemp1_1.JPG http://imgur.com/sgRI8FM" onclick="window.open(this.href);return false;

ViewAllTemp1_1.JPG

The point I am making is that you can, in English Excel , just copy the formula from your formula bar to get the general Formula form that you need to start with.



Finally for now: ( Just to clarify once again for anyone else following this Thread):
I am just for convenience taking that formula from the row just before the row we want to start filling in formulas of that type.
I just do that through copying a Debug.Printed formula to get the approximate form of the English version of that formula.
I do not use that formula at all in actually achieving the main requirement of this Thread, which is filling in some formulas over more rows ( Filling the spreadsheet up with formulas down a few more rows than they already are ).
I am not getting the formula to copy / paste into the following rows.

The Current code does:…
The basic formula is changed into a dynamic form , where the row number is dynamic.
In an internal VBA code Loop, is built up a large “internal VBA Array” of the entire formulas as Strings ( held in Variant type member Elements of that Array ) . These are then later, ( after the entire internal Array is filled ), pasted out in one go over the Range we want to fit in.
In the demo screenshot above we only have one row to fill in. That is just for demonstration purposes. In the practice the rows to be filled in may be much more. In the very original codes at the start of the Thread, that last formula was copied in order to use in a copy / paste type action: That copied formula was then pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down.
Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down.Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down.……. :)
We are looking at other ways to fill in the formulas.
:)


Alan

[bknight]

Hi

Just some quick very minor initial points, - ( I have not had time to look in too much detail fully yet: )

_(i) Avoid using the pointed brackets ( > < ) in ‘comments ( and codes ) as much as possible. You may have to use them in codes – If so, fair enough, - do it.
I just mention it in passing as although I have not had a problem here at Eileen’s Lounge yet, I do find that posting any code generally around the internet can sometimes causes problems when a pointy bracket is mistaken as a “HTML Code Tag thingy”

OK, I used the ---> to emphasize what comments that were deleted to fit into the size limitations

....
_ (ii) Avoid the extra brackets like in (Lr = 2) above. It is a bad habit of mine which in some situations may catch you out as it may do something you did not want.
I do often use extra brackets. Just a personal preference. A bad Habit. I have them. :)
One reason for having a syntax in a code ( or an order of mathematical operations ) is to avoid a periphery of brackets.
This code line_...
...

I like a periphery of brackets and the such. I do not know why. Maybe I am something of a anarchist programmer. I guess VBA is a anarchistic programming language actually. So it suits ? ( VBA ( so experts tell me ) rejects sometimes the Object Orientated Programming language hierarchy, which we are continually told it adheres to )

I use the extra brackets to ensure the calculation meets what math I intend, but I understand your basic theme in usage of brackets.

...
_(iv) In your code from the last post you forgot your Sr calculation code line for Vix worksheet

Code: Select all

Sr = (ws.Cells(ws.Rows.Count, "F").End(xlUp).Row) + 1

Ok, I will add it to the code, the after sheet that was linked wasn't run with code, just auto fill of the formulas, but I will test it today.

...
Finally for now: ( Just to clarify once again for anyone else following this Thread):
I am just for convenience taking that formula from the row just before the row we want to start filling in formulas of that type.
I just do that through copying a Debug.Printed formula to get the approximate form of the English version of that formula.
I do not use that formula at all in actually achieving the main requirement of this Thread, which is filling in some formulas over more rows ( Filling the spreadsheet up with formulas down a few more rows than they already are ).
I am not getting the formula to copy / paste into the following rows.
...
The Current code does:…
The basic formula is changed into a dynamic form , where the row number is dynamic.
In an internal VBA code Loop, is built up a large “internal VBA Array” of the entire formulas as Strings ( held in Variant type member Elements of that Array ) . These are then later, ( after the entire internal Array is filled ), pasted out in one go over the Range we want to fit in.
In the demo screenshot above we only have one row to fill in. That is just for demonstration purposes. In the practice the rows to be filled in may be much more. In the very original codes at the start of the Thread, that last formula was copied in order to use in a copy / paste type action: That copied formula was then pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down.
Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down.Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down. Then that just pasted formula was copied and pasted one row down.……. :)
We are looking at other ways to fill in the formulas.
:)


Alan

I still don't fully understand the scheme behind your coding structure, but it works and that was the main goal in the initial question.

[Doc.AElstein]

Hi

Some other very minor points again that I could not fit in..

_( viii) You do not usually need to Activate your Worksheets for such codes to work. But it is very helpful in Developing. We need to see and have the Worksheet Active to do anything with it manually. VBA does not. We often need to Select things as well in order to do anything with them. That is one of the first things you learn as being a major difference between :
_ a code got from a macro recording
and ;
_ a code you write from scratch yourself.
You will need to both Activate and Select things to do anything. The macro recorder will record that. When optimizing a code obtained from a macro recorder, you usually take out most of the .Activate and .Select bits

_(ix) I expect all this you will notice yourself when you check, but as I noticed anyway: .. - Your Before and After may be a bit mixed up, or not, depending on exactly what you want.

_Your Before and After are the same for Worksheet OEX. I expect you may have just not given the correct After there? And/ or you are not using the correct columns for the Lr and Sr calculation? But I may have missed exactly what you are wanting to do there. I think that is up to you to check or sort out. Possibly it is what you want.

_ For the Before and After for Calc –… the After has a complete new row of data compared with the Before.
The code gives some strange results ( as expected ) in that case. This is because the code was written such that Lr is where the range to be added ends, and Sr is where it starts. If you think about it then that idea will not work if the whole row is filled in. But that is up to you to check or sort out.

You can think of some way to do a check for something like that with like..
If Sr = lr + 1 Then
‘ don’t do anything for that worksheet or give a message saying “ something is wrong”
Else
‘ Do it all
End If

But that will all depend of course on what you actually want to do, should the whole row be filled. In the case of your Before and After for Calc there is no way to guess you wanted to add a complete new row. I expect you may have intended a before to have already something at least one column ? Or you use the Sr and Lr from the previous worksheet? I can really not guess there.
_....

I expect then the OEX and Calc sections you need to check , and / or your before and After for those worksheets. But I will not look further there. If I take a look possibly, out of interest at a different solution idea, then I will just do the Vix Worksheet Snippet again as an example…. _..
_..But anyway I think it is probably solved enough now for you..

..it works and that was the main goal in the initial question.

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

…I still don't fully understand the scheme behind your coding structure...

I think when you have time if you re read all I have written again, then it will suddenly become obvious. It’s one of these things that once you know is ridiculously obvious, but it just takes a while to suddenly twig.

The important point to note is that, ( ignoring the Debug.Print stuff for now as that is not really part of the main code idea – I think you understand that. ) , my main code idea Copies nothing.
It makes all the formulas you need and stores them all in a bit of internal memory somewhere. It pastes them all out in one go over the entire range. How VBA actually does that is lost deep down in the internal workings. I have no idea how it does that exactly. I doubt many people do.


This layman’s explanation will do:

Each Loop in the code puts very quickly all the formulas that you want for a range to be filled in , in your hand.
Think of your hand as that internal bit of memory space.
If you like, think of your hand as looking a bit like a spreadsheet but a small spreadsheet with only just enough “rows” and “columns” for all the formulas you want, so it is only just big enough to hold all those formulas in it.
The Loop makes and puts those formulas into that “hand”. It puts them in a “row” and “column” order that is just the same as the actual row and column order that you finally want in your actual spreadsheet.
The Loop does nothing at all to the actual worksheet. - Your hand with all the final formulas in it is totally independent to the Worksheet.

Now, Say you stopped the code just after the Loop and just before this line
Let rngWs.Value = arrrngWs()

You then left your computer at that point and left the worksheet on your screen where it was and you still had all the formulas in your hand last night when you went to the party. You still had the formulas in your hand when you got back.
When you got back , the Worksheet was still showing on your screen. You were just about sober enough to hit the correc keyboard keys, so then you let the code go further such that this line_...
Let rngWs.Value = arrrngWs()
_....was done. That line is doing like you chucking all those formulas in your hand at your screen such that they all land at once in the correct cells.


Looking at the hand idea for your very original code. That was like you sort of copying manually each row of formulas , adjusting them for the next row offset, then chucking it back at the next row down. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen.


My last attempt, just a bit of fun..: :xsmile:

My Code idea:

Go to the corner shop. Fill up a box of sweets, ( marking each one with a kids name on it ) for a kids party at your home. Go home chuck all the sweets out and all the kids catch them willingly in one go.

Original Code:
Go to a big supermarket somewhere in a big shopping center. Buy a sweet. Put a kids name on it . Go home to a kids party and give the sweet to a kid. Go to a big supermarket somewhere in a big shopping center. . Buy a sweet. Put a kids name on it. Go home to a kids party and give the sweet to a kid. Go to a big supermarket somewhere in a big shopping center. . Buy a sweet. Put a kids name on it . Go home to a kids party and give the sweet to a kid. Go to a big supermarket somewhere in a big shopping center. . Buy a sweet. Put a kids name on it. Go home to a kids party and give the sweet to a kid. Go to a big supermarket somewhere in a big shopping center. . Buy a sweet. Put a kids name on it. Go home to a kids party and give the sweet to a kid.


You will kick yourself when you finally realize how simple that is to understand. Just maybe you need to get clued up a bit on VBA Arrays.

[Doc.AElstein]

:xsmile: :xsmile:

Code: Select all

    Let arrrngWs(Cntrw, 4) = "=AVERAGE(E" & Sr - 50 + Cntrw & ":E" & Sr - 1 + Cntrw & ")"
    Next Cntrw
 ' Go to a Party at this point. Take arrrngWs with you if you like. It might be a novel ice-breaker  ;)
 Let rngWs.Value = arrrngWs() ' Fill up the worksheet in one go if you are  still sober wenn you  get back, by chucking arrrngWs with all the formulas in it at the screen

:xsmile: :xsmile:

[bknight]

Hi

...
_Your Before and After are the same for Worksheet OEX. I expect you may have just not given the correct After there? And/ or you are not using the correct columns for the Lr and Sr calculation? But I may have missed exactly what you are wanting to do there. I think that is up to you to check or sort out. Possibly it is what you want.

_ For the Before and After for Calc –… the After has a complete new row of data compared with the Before.
The code gives some strange results ( as expected ) in that case. This is because the code was written such that Lr is where the range to be added ends, and Sr is where it starts. If you think about it then that idea will not work if the whole row is filled in. But that is up to you to check or sort out.




...
I expect then the OEX and Calc sections you need to check , and / or your before and After for those worksheets. But I will not look further there. If I take a look possibly, out of interest at a different solution idea, then I will just do the Vix Worksheet Snippet again as an example…. _..
_..But anyway I think it is probably solved enough now for you..

..it works and that was the main goal in the initial question.

Yes you are correct cells "OEX!H3551" should be blank. All sheets should have data added for 12/15/2016, without calculations. Remember all cells on "Calc" are formula links to the other sheets, so they should be blank until the macro is executed.

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

…I still don't fully understand the scheme behind your coding structure...

I think when you have time if you re read all I have written again, then it will suddenly become obvious. It’s one of these things that once you know is ridiculously obvious, but it just takes a while to suddenly twig.

...

This layman’s explanation will do:

Each Loop in the code puts very quickly all the formulas that you want for a range to be filled in , in your hand.
Think of your hand as that internal bit of memory space.
If you like, think of your hand as looking a bit like a spreadsheet but a small spreadsheet with only just enough “rows” and “columns” for all the formulas you want, so it is only just big enough to hold all those formulas in it.
The Loop makes and puts those formulas into that “hand”. It puts them in a “row” and “column” order that is just the same as the actual row and column order that you finally want in your actual spreadsheet.
The Loop does nothing at all to the actual worksheet. - Your hand with all the final formulas in it is totally independent to the Worksheet.

Now, Say you stopped the code just after the Loop and just before this line
Let rngWs.Value = arrrngWs()

You then left your computer at that point and left the worksheet on your screen where it was and you still had all the formulas in your hand last night when you went to the party. You still had the formulas in your hand when you got back.
When you got back , the Worksheet was still showing on your screen. You were just about sober enough to hit the correc keyboard keys, so then you let the code go further such that this line_...
Let rngWs.Value = arrrngWs()
_....was done. That line is doing like you chucking all those formulas in your hand at your screen such that they all land at once in the correct cells.


Looking at the hand idea for your very original code. That was like you sort of copying manually each row of formulas , adjusting them for the next row offset, then chucking it back at the next row down. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen. Then you sort of copying manually that new row, changing that for the next offset row and chucking it back at the screen.


My last attempt, just a bit of fun..: :xsmile:

My Code idea:

Go to the corner shop. Fill up a box of sweets, ( marking each one with a kids name on it ) for a kids party at your home. Go home chuck all the sweets out and all the kids catch them willingly in one go.

Original Code:
Go to a big supermarket somewhere in a big shopping center. Buy a sweet. Put a kids name on it . Go home to a kids party and give the sweet to a kid. Go to a big supermarket somewhere in a big shopping center. . Buy a sweet. Put a kids name on it. Go home to a kids party and give the sweet to a kid. Go to a big supermarket somewhere in a big shopping center. . Buy a sweet. Put a kids name on it . Go home to a kids party and give the sweet to a kid. Go to a big supermarket somewhere in a big shopping center. . Buy a sweet. Put a kids name on it. Go home to a kids party and give the sweet to a kid. Go to a big supermarket somewhere in a big shopping center. . Buy a sweet. Put a kids name on it. Go home to a kids party and give the sweet to a kid.


You will kick yourself when you finally realize how simple that is to understand. Just maybe you need to get clued up a bit on VBA Arrays.

I still haven't run the macro, doing other normal tasks on Saturday morning, but soon.

[bknight]

Upon checking code this morning

Code: Select all

Sr = (ws.Cells(ws.Rows.Count, "F").End(xlUp).Row) + 1

Is in the steps, must have deleted that step while deleting comments, original had a do loop directly ahead('down") in the code that was deleted.

[bknight]

Ok, I'm on the verge of getting everything working, but am running into a problem formatting a cell.
In my original method of copy/paste method the formats were carried over to the target. Using the array method of pasting formulas into cells, doesn't have that luxury.

I have tried both
Cells(Sr, 1).NumberFormat = "mm/d/yyyy;@"
AND
Range(Cells(Sr, 1), Cells(Sr, 1)).NumberFormat = "mm/d/yyyy;@"

Neither work, so what is the correct format code?

[Rudi]

You's have to use a DateSerial function to convert it into a valid date...
(Note: The function assumes a number/string in yyyymmdd format. You'd have to change to formula if your date is in a different format)

Code: Select all

Sub NumToDate()
    v = Cells(Sr, 1)
    ' convert it to a string
    s = CStr(l)
    ' can now use string functions to parse it (assumes yyyymmdd)
    d = DateSerial(CInt(Left(s, 4)), CInt(Mid(s, 5, 2)), CInt(Right(s, 2)))
    ' write it back to the sheet
    Cells(Sr, 1) = d
End Sub