Macro not working as expected

[Doc.AElstein]

Hi
As I mentioned I will drop you off a demo of the other more typical ways of doing what you want.

Dates can be a problem in these sorts of cases.
http://www.eileenslounge.com/viewtopic. ... 87#p196639" onclick="window.open(this.href);return false;
Alan

[bknight]

In the particular cell the value is 42720, which would be 12/16/2016

[Doc.AElstein]

In the particular cell the value is 42720, which would be 12/16/2016

Correct, that is what you annoyingly get sometimes. It is the most fundamental Range Object "values" property of .Value2
For a date it is a Double Number -
The whole part is the day starting from somewhere around 1900 ( I forgot when exactly ) .
The fractional part is the fractional time of the day , I think.
Range Object "values" property of .Value usually catches the date Format ( not the time I think )

Dates and time Formats are always a pain I find in Excel and VBA

[HansV]

Instead of

Cells(Sr, 1).NumberFormat = "mm/d/yyyy;@"

use

rngWs.NumberFormat = "mm/d/yyyy;@"

[bknight]

Instead of

Cells(Sr, 1).NumberFormat = "mm/d/yyyy;@"

use

rngWs.NumberFormat = "mm/d/yyyy;@"

Stupid question, why doesn't
Range(Cells(Sr, 1), Cells(Sr, 1)).NumberFormat = "mm/d/yy;@"
work?

[Doc.AElstein]

For one thing you are relying on Excel "guessing" where your Cells and Range are.
Depending where the code is , and / or what Worksheet is active, you will may be referrenceing different Worksheets

[bknight]

Shouldn't be an issues only one workbook open and the specific sheet is active, however I changed the code to reference that specific range.
Seems to work.

[HansV]

Stupid question, why doesn't
Range(Cells(Sr, 1), Cells(Sr, 1)).NumberFormat = "mm/d/yy;@"
work?

Range(Cells(Sr, 1), Cells(Sr, 1)) is equivalent to Cells(Sr, 1), so it is only a single cell.

If I understand the code correctly, rngWs is the entire range the formula/value is being placed in.

[bknight]

Code: Select all

Set rngWs = Worksheets("Calc").Range("A" & Lr & ":A" & Lr & "")
rngWs.NumberFormat = "mm/dd/yy;@"
Set rngWs = Worksheets("Calc").Range("E" & Lr & ":E" & Lr & "")
rngWs.NumberFormat = "mm/dd/yyyy;@"

This does not format the cell to the desired format, both cells contain 42720..

EDIT: I realize they are single cells, but do not understand the nuances of excel range differences from a multicell to a single cell

[Doc.AElstein]

.... I realize they are single cells, but do not understand the nuances of excel range differences from a multicell to a single cell

You can do it your way. That is just saying that the cell range starts at a cell address and finishes at the same cell address
Like pseudo
Range(startcelladdress:stopcelladdress)
or
Range(topleftcelladdress:bottomrightcelladdress)
If you miss out the : then using the column letter and column number address notation ( A1 type or $A$1 ) then pseudo VBA is expecting
Range(TheSingleCellAddress)

[Doc.AElstein]

Hi,
I just got your PM saying you do not want this answered now, but I had just finished so I will post anyway for completeness.

...I just had another thought while penning a response. IF
Set rngWs = Worksheets("OEX").Range("G" & Sr & ":H" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 1) should be ReDim arrrngWs(1 To rngWs.Rows.Count, 2 To 2)
THEN
For Cntrw = 2 To (UBound(arrrngWs(), 1) - 0
Let arrrngWs(Cntrw, 2) = "=SUM(F" & Sr - 160 + Cntrw & ":F" & Sr - 1 + Cntrw & ")/160"
What do you think?

_.. I am ( was - maybe I have it now ) not quite following you. When I questioned that the Before and After were the same
http://www.eileenslounge.com/viewtopic. ... 80#p197565" onclick="window.open(this.href);return false;
you said "OEX!H3551 should be blank”
http://www.eileenslounge.com/viewtopic. ... 80#p197569" onclick="window.open(this.href);return false;

_ That implied this for before
OEXBefore.JPG

OEXBefore.JPG

And this for after
OEXAfter.JPG

OEXAfter.JPG

The OEX code section is then changed appropriately in a similar ways as you have done with all the code sections for the different worksheets.

Sr uses column H to determine the start row.

The start column and stop column of the Range are in this cases both H
The Array required is that to suit a 2 Dimensional 1 column Array. Possibly here lies the confusion.
When dealing with Spreadsheet Ranges we are talking generally about a 2 Dimensional Areas. Correspondingly when getting the values from a single row you will generally get a 1 row, 2 Dimensional Array .
If , as in our case, we want to create an Array to paste out to a column then we need a 1 column, 2 Dimensional Array.

This is the full code snippet that works with the last files from you modified for the correct Before
( the formula I adjusted in two places by 1 row as well to get the correct formula output )

Code: Select all

 ' OEX   '     "OEX!H3551" should be blank.
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) = "=SUM(F" & Sr - 160 + Cntrw & ":F" & Sr - 1 + Cntrw & ")/160"
    Next Cntrw
Let rngWs.Value = arrrngWs() ' This will paste out the Formula values to the Worksheet

( EDIT: Minor point just to avoid confuson: A 1 dimensional Array has no orientaion, but VBA just as a convention "takes" them as a "horizontal" Array when you apply them to a spreadsheet. )

[Doc.AElstein]

I cannot help too much with the Calc code section date issue as the last Calc code section I had from you was using columns which had the same last row. Hence Sr and Lr were the same and the code as written was not going to work anyway.
How are you determining those Sr and Lr currently?
What is the current Before and After looking like for Worksheets calc?

[bknight]

Code: Select all

Sub CopyCellsFormulas()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim dteDateValue As Date
    Dim R As Integer
    Dim Lr As Long, Sr As Long, Cntrw As Long
    Dim Temp() As Variant
    Dim strTemp As String
    Dim rngWs As Range 
    Dim arrrngWs() As Variant
    Set wb = Workbooks("VIXData050824.xls")
    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

Set ws = Worksheets("Vix")
Worksheets("Vix").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("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() ' This will paste out the Formula values to the Worksheet
Cells(Lr + 1, 2).Select

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$3)+(E" & Sr - 1 + Cntrw & "*$I$2)"
     Let arrrngWs(Cntrw, 3) = "=F" & Sr - 2 + Cntrw & "/G" & 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 & "")
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

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
Let strTemp = ws.Range("H" & (Sr - 1) & ":H" & (Sr - 1) & "").Formula
'Let strTemp = ws.Range("H" & (Sr - 1) & "").Formula
Debug.Print strTemp    ' =(F3261*$I$3)+(E3262*$I$2)
Set rngWs = Worksheets("OEX").Range("H" & Sr & ":H" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 1) ' We must use Redim as this allows us to use the Rows Count Property
    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

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 & ":G" & Sr & "")
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

Finished with exception of the formatting as discussed. I know Hans will not like me using Cells(Lr,1).Select but it was the only way my poor coding skills could make it work.

[bknight]

I cannot help too much with the Calc code section date issue as the last Calc code section I had from you was using columns which had the same last row. Hence Sr and Lr were the same and the code as written was not going to work anyway.
How are you determining those Sr and Lr currently?
What is the current Before and After looking like for Worksheets calc?

The code executes including the "Calc" sheet, even formatting the cells discussed earlier. I used as you will see the strTemp idea you posted earlier for "OEX" I will update the links shortly.

[bknight]

I cannot help too much with the Calc code section date issue as the last Calc code section I had from you was using columns which had the same last row. Hence Sr and Lr were the same and the code as written was not going to work anyway.
How are you determining those Sr and Lr currently?
What is the current Before and After looking like for Worksheets calc?

The code executes including the "Calc" sheet, even formatting the cells discussed earlier. I used as you will see the strTemp idea you posted earlier for "OEX" I will update the links shortly.

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

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

[Doc.AElstein]

Hi

Some other very minor points
_(x) You are declaring some variables but not always use them as you could to simplify the code a bit.
For example as you do this_....
Dim ws As Worksheet
_ Set ws = Worksheets("Vix")
_.....you may change this _.........
Worksheets("Vix").Activate
_............to this
ws.Activate

_ ( Probably Dim Ws As Worksheet is a bit better practice, as I mentioned, I think. )

A more important point

For Calc Worksheet you only ever add one row.
( I do note, that in your example Before / After and code variations you only added one row, but I assumed that was just to simplify the Before and After demo. – I suggested a few rows might be better )

( _ You have modified the code for Calc so that you use SR for both the start and the stop row. Hence your range to fill in will always be one row )

With the exception of the Calc Worksheet the code will add any amount of rows – however many is needed. I thought that is what you wanted . If not fine._.........
_..........._ – But you will need to run the code every time to add each row.

[bknight]

Alan:
The specific case is one day at a time calculation, as that is the normal day to day download/calculation operation, however any suggestion to code changes to a general case are willingly accepted. The reason I used the start date and end date identical, is that the code was adding another day's calculation one row down, all full of zeros, as there was not any data/calculations to link.

[Doc.AElstein]

Hi
_1) For just adding one line , of course , your very original code is fine. - The problems , as far as I can remember, came when you tried to do a lot of rows.

_2a) For the general case with the current code , you just need to find a way to determine the last and start row.
In all the other Worksheets you could chose a column not yet filled in for last row, Sr ( Sr being determined by us as
= ( the last row filled in in any column to be filled ) +1
For Calc that is any column. So any column will do for Sr . Sr is not the problem

Start Row, Lr, in all other Worksheets is determined from a column already filled.

I assume the above is what is required, and that those are the typical start points

_2b) In the Afters that I have seen so far no columns were filled in yet for Calc - So the current method for finding Sr is not possible.

Any code , of any type, regardless of how it is modified or changed will have to know how many rows are to be filled in. It is obvious from the other Worksheets, at least if the logic from _2a) is correct.
I do not know enough about the entire project to know how many rows are likely to be needed to be added in Calc at any particular time

EDIT 20th Dec - I had my Sr and Lr mixed up

[bknight]

I can think of two possible solutions.
The "Calc" can only have linked cells to the last cell in any of the other sheets, so a method of "evaluating " array members for zero values and only "pasting" values when non zero values are evaluated.
The other possible solution, would be to evaluate the array member associated with date column versus "today" the array evaluation can not be greater than today.

[Doc.AElstein]

Anything will do that will get you Sr or the number of rows to be added.
VBA will "let you get at" most things.
If you have anything from which you can derive the number of rows to be added, then there is likely some way to get a code to get that infomation in a way such as to calculate Sr, or just the entire row count.
Once again I do not exactly follow what you are referring to.
There are many ways to check for zeros in differnt things. Depends on the exact example.
Remenber once again, the current code Pastes out all in one go.. So you cannot "paste a row If"
But you can change things and look for things in the Array before pasting it out