Macro not working as expected

[bknight]

Hi
I think you have given one wrong link -
You have given the same link for both files.
Alan

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

[Doc.AElstein]

Hi
Thanks fo the new link

Alan

[Doc.AElstein]

Hi
I changed your start file very slightly to this, just to show a few more rows to be filled in:
I will only consider for now Worksheets “Vix”
Here is start ( Before )
VixBefore.jpg http://imgur.com/4x00adY" onclick="window.open(this.href);return false;

VixBefore.JPG

here is your After
VixAfter.JPG http://imgur.com/qoBVdnm" onclick="window.open(this.href);return false;

VixAfter.JPG

OK.

I am looking at the “Array Code” solution. It only interacts with the spreadsheet a few times. So the improvement with these things _..

Code: Select all

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

_.. is probably very small. So to keep it a bit simpler I would recommend initially doing away with that.
( You can run into problems in development when using those. For example if a code crashes it may be left in a state like, Application.ScreenUpdating = False , in which case your screen may appear “dead” after that. ( Error handling can help overcome that, but we need to learn to walk before we can learn to run.. Lol.. :) ). So leave those out initially. Or to be on the safe side change them to_..

Code: Select all

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic

_.. This is just to be on the safe side during development. – We may have inadvertently left them in the False / xlCalculationManual state at some point when developing the code. )

_...



I will just do the example for Worksheets “Vix”. I will just modify your last code up until just after that Worksheet has the formulas filled in. I do not want to do the whole code in case I have missed the point again.
I will now explain the code part given below, with emphasis on the bits I change.


I Dim the worksheet ( and many other variables) just before the first time I use them.
I often do that with variables. That is just a bad habit of mine as I am not a Computer Professional.
( I also use Let a lot. I have a reason for that, but just ignore it as another bad habit of mine )

I include at the start of the Module, before any code
Option Explicit
That forces me to Declare ( Dim ) all variables
Having done that, The code errors on attempting to start, telling me that lr has not been declared. ( Rudi mentioned that in his last post )
So I declared it. ( I also use upper case L . That way when I write lower case l , the complier changes it to upper case. I recommend you doing that. Then if you write all in lower case and then the complier does not change to your declared version with upper case, then you know you typed something in wrong )

Lr is giving me the last row that I need to fill in with formulas ( I think) . ( 3262 )

I need to know the row where I need to start filling in formulas. I use a variable Sr for that. The code determines that start row as the next empty cell in column F ( I have another bad habit of using extra brackets in code lines to help me see what I am doing )
( Sr comes out as 3260 for the example shown in the screenshot )

( The next code section Rem Code section to help get Formulas you can ignore. I use it to help get the formulas I need – I use it to print out for me the 4 Formulas in the last filled F – I row )


Range Object, rngVix, is in my understanding now, the Range to have formulas added. ( previously I was not too clear what was going on so had some weird Range extending over existing formulas ) . For the example in the screenshot it is for 3 rows.

At this point I start using my second code snippet suggestion.
http://www.eileenslounge.com/viewtopic. ... 21#p197190" onclick="window.open(this.href);return false;
I make an “internal VBA Array”, arrrngVix(), to hold all the formulas you want in rngVix

It is an Array of size ( 1 To 3 rows , 1 To 4 columns ) – This is columns F to I, and the three rows required for the screen shot example

It is filled with the exact formulas requires for all 3 rows x 4 columns.

Finally in the last line of the code snippet below , the Array is pasted out to the Spreadsheet

At this point the Before and After for Worksheets “Vix” are identical


Alan

I will drop my Code snippet in next post ( I measure approx 7300 characters for this post including the Code, but the Forum tells me it is approx 7650 characters ( The limit is 7500 per post, I believe ) ). Never mind

[Doc.AElstein]

Code snippet for last post

Code: Select all

Sub CopyCellsFormulas()
    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")
 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() = 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)

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

Alan

[bknight]

At this point the Before and After for Worksheets “Vix” are identical

If this is correct then it is what I am seeking

Code snippet for last post

Code: Select all

Sub CopyCellsFormulas()
    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")
 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() = 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)

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

Alan

Code: Select all

     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 & ")"   '

Without a great amount of analyzing, why the parenthesis in the first statement and not in the other three.

The code needs to be duplicated, with different Cntrw, (Cntrw1, Cntrw2, Cntrw3). Then the affected columns in the rest of the sheets will need to be altered.

[Doc.AElstein]

Hi bknight

At this point the Before and After for Worksheets “Vix” are identical

If this is correct then it is what I am seeking

It appears to me to give identical results. Yesterday I tested it over a larger range and it gave what appeared to be the correct results – it gave the same results yesterday as your code , ( but without crashing ), in the First File you uploaded.

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

...Without a great amount of analyzing, why the parenthesis in the first statement and not in the other three...

I did not give it a lot of thought. – I just copied the formula that is there
FFormula.JPG http://imgur.com/0RzGXQI" onclick="window.open(this.href);return false;

FFormula.JPG

_... or rather I copied initially what I got from Debug.Print, and then adjusted it to be dynamic for different rows )
DebugPrintVix.JPG http://imgur.com/RDYv7X5" onclick="window.open(this.href);return false;

DebugPrintVix.JPG

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

....
The code needs to be duplicated, ..... Then the affected columns in the rest of the sheets will need to be altered.

Probably. ( I just took a look at the other Worksheets and the Before and After Files appear to be the same for those other worksheets.? )

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

.....
The code needs to be duplicated, with different Cntrw, (Cntrw1, Cntrw2, Cntrw3). .....

Cntrw is the Loop Bound variable count, it takes on the value 1, 2, 3, 4 etc.. up to the maximum rows to have formulas put in them . You can use the same variable, Cntrw . in all Loops
It is just personal preference. Most people would use the same variable , Cntrw for all loops.
If it were my code, I would do it untypically. I would have a Cntrw for each Worksheet, like CntrwVix, CntrwPCRat etc. - But that is an untypical personal preference. It declares extra variables unnecessary.



Alan

[bknight]

Cntrw is the Loop Bound variable count, it takes on the value 1, 2, 3, 4 etc.. up to the maximum rows to have formulas put in them . You can use the same variable, Cntrw . in all Loops
It is just personal preference. Most people would use the same variable , Cntrw for all loops.
If it were my code, I would do it untypically. I would have a Cntrw for each Worksheet, like CntrwVix, CntrwPCRat etc. - But that is an untypical personal preference. It declares extra variables unnecessary.

When coding the initial steps the other night, I got a Duplicate declaration error, until I changed them as I posted. Look at code in either file I uploaded.

[Doc.AElstein]

Hi bknight,

Cntrw is the Loop Bound variable count, it takes on the value 1, 2, 3, 4 etc.. up to the maximum rows to have formulas put in them . You can use the same variable, Cntrw . in all Loops......

When coding the initial steps the other night, I got a Duplicate declaration error, until I changed them as I posted. ....

You can re Set = ( and re Let =) a variable as much and as often as you like.
In my Layman’s way of thinking about it that is like putting something different in the variable.
You can do that at any time in and at any point in a code after a variable has been declared ( Dim – ed )

Similarly you can use it in all your loops like
___ For Cntrw = 1 To (UBound(arrrngOex(), 1) - 1)

or / and then also later in a differnt Loop*** use it for like

___ For Cntrw = 1 To (UBound(arrrngCalc(), 1) - 1)

(*** Note: the Loop you first use it in must be finished before you use it again, or VBA will get confused )



Dim is only read once. It sets the variable type. In layman’s terms makes it of a type that can “handle” the sort of thing you are going to “put into it”. Generally you can only do that once.
That is one reason I guess why most people do all there declaring at the start of the code.

It is normal code practice to put the declarations at the start of routine ( Or at the start of a module - ( where they must be for variables declred outside a routine ) ) .

It is just a personal preference of mine not to always put the declarations within a routine at the start of the routine . I Dim often , as I mentioned, just before I use the variable the first time. Sorry if that confuses.

So you can use Cntrw as the Loop Bound variable Count in all your loops. But just Dim it the once. Dim it anywhere you choose as long as it is before the first time you use it.

Sorry for any confusion.

As I mentioned, I would always use different variables anyway. I find then in developing that I can easier keep track of what is going on and where it is going on.
You have also used a different variable for your Arrays and Ranges, like rngCalc , rngOex etc. I would do that also. Many would just do a Dim of a rng, and an arr() and use those everytime.
By using a different varibale, you can stop the code at some late point and go back and see what is in all the Ranges and Arrays if you use a different variable for each Range and Array.
That is the main reason why I prefer to use more variables.
In the case of Cntrw there are not many advantages in using a different variable, other than it ensures you do not accidentally use the same varible before a loop in finished.
For example, In codes like the original Snippet I did, there is a nested Loop. That is to say
___ For Cntrw
___starts, and before it is finished
______For Cntclm
______starts
if you used the same varible, such as Cnt for those two then you would get VBA upset / mixed up, and it would error ( I think )

Alan

[bknight]

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 ' variable to hold the entire Range you are currently considering to add formulers to
    Dim arrrngWs() 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
    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"

.....

Let Temp() = Worksheets("Vix").Range("F" & (Sr - 1) & ":I" & (Sr - 1) & "").Formula
....
Let Temp() = Worksheets("PutCall Ratio").Range("F" & (Sr - 1) & ":H" & (Sr - 1) & "").Formula
....
Let Temp() = Worksheets("OEX").Range("H" & (Sr - 1) & ":H" & (Sr - 1)& "").Formula
...
End Sub

I'm having trouble with the third Temp() statement. The first two run fine no error. When I get to the third I get a runtime error 13 Type Mismatch
Any suggestions? I have tried deleting the last ampersand and quotation marks, but I get the same error.

[HansV]

Range("H" & (Sr - 1) & ":H" & (Sr - 1)& "") is a single cell, and therefore, Range("H" & (Sr - 1) & ":H" & (Sr - 1)& "").Formula is a single string, not an array of strings.

How do you use Temp after that third statement?

[bknight]

Code: Select all

Debug.Print Temp(1, 1) ' =(F3312*$I$3)+(E3313*$I$2)Lr=3315 Sr=3314
Set rngWs = Worksheets("OEX").Range("H" & Sr & ":H" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 4) ' 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 ...
...

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

[bknight]

Code: Select all

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
    '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
Let Temp() = Worksheets("PutCall Ratio").Range("F" & (Sr - 1) & ":H" & (Sr - 1) & "").Formula
Debug.Print Temp(1, 1) ' =(F3312*$I$3)+(E3313*$I$2)Lr=3315 Sr=3314
Debug.Print Temp(1, 2) ' =SUM(E3233:E3313)/81
Debug.Print Temp(1, 3) ' =F3313/G3313
Set rngWs = Worksheets("PutCall Ratio").Range("F" & Sr & ":H" & Lr & "")
ReDim arrrngWs(1 To rngWs.Rows.Count, 1 To 4) ' 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) = "=(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() ' This will paste out the Formula values to the Worksheet

The whole part before where I'm having issues.

[HansV]

1) Change all occurrences of Temp() to Temp

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

[bknight]

1) Change all occurrences of Temp() to Temp

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

That would be the next step, I didn't post it, because I'm still in the process of coding.
After changing the Temp() to Temp
The first two executed but the third still has a runtime error 13.
Any other suggestions to obtain the formula?

[HansV]

Did you change Temp() to Temp in the declaration (Dim Temp() As Variant)?

After the line

Let Temp = Worksheets("OEX").Range("H" & (Sr - 1) & ":H" & (Sr - 1)& "").Formula

you should refer to Temp, not to Temp(1, 1) or similar.

[Doc.AElstein]

Hi,
In line with what Han’s has already said and to explain a bit what is going on.

Various “values” Properties are available in a Range Object. For example .Value and .Formula

If the Range is greater than one Cell than you are referring to a Field ( Array ) of Variant types.

My code snippet was looking at a single row of 4 columns, so 4 cells. ( with formulas in them )
.Formula therefore returned a Field of 4 formulas held in Variant type Elements
So appropriately my variable to “take” that returned Filed was an array
Temp()
with types declared as Variant
Dim Temp() As Variant


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


If the Range is a single Cell , then as Hans said you will not get a Field , but a single value is returned.

Equating an Array to a single value will give you the mismatch type error you observed

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

Worksheets("OEX").Range("H" & (Sr - 1) & ":H" & (Sr - 1)& "").Formula

Is the same as

Worksheets("OEX").Range("H" & (Sr - 1) & “”).Formula

That is similar to a line like

Worksheets("OEX").Range("H2”).Formula
And
Worksheets("OEX").Range("H2:H2”).Formula

The above will return a string of a formula, ( assuming the cell has a Formula in it )

The appropriate variable type to “take” that would be, for example a String, like

Dim strtemp As String
Let strTemp = Worksheets("OEX").Range("H" & (Sr - 1) & “”).Formula

then you do_...
Debug.Print strTemp
_....in order to see that single formula string in the immediate window

[bknight]

Did you change Temp() to Temp in the declaration (Dim Temp() As Variant)?

After the line

Let Temp = Worksheets("OEX").Range("H" & (Sr - 1) & ":H" & (Sr - 1)& "").Formula

you should refer to Temp, not to Temp(1, 1) or similar.

Never got that far, but understood.
After change the dimension declaration, now I get a runtime error 9 Subscript out of range. at
Set ws = Worksheets("Vix")-->The first part of the sub.

[HansV]

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

[bknight]

...


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


If the Range is a single Cell , then as Hans said you will not get a Field , but a single value is returned.

Equating an Array to a single value will give you the mismatch type error you observed

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

Worksheets("OEX").Range("H" & (Sr - 1) & ":H" & (Sr - 1)& "").Formula

This does not work either, gives runtime error 438 Object doesn't support this property or method.

Is the same as

Worksheets("OEX").Range("H" & (Sr - 1) & “”).Formula

That is similar to a line like

Worksheets("OEX").Range("H2”).Formula
And
Worksheets("OEX").Range("H2:H2”).Formula

The above will return a string of a formula, ( assuming the cell has a Formula in it )

The appropriate variable type to “take” that would be, for example a String, like

Dim strtemp As String
Let strTemp = Worksheets("OEX").Range("H" & (Sr - 1) & “”).Formula

then you do_...
Debug.Print strTemp
_....in order to see that single formula string in the immediate window

The code needs to get to that statement firstly.
I have a solution to the problems set the range to G and H even though G contains a number, and then get the formula in H.
What do you guys think?

[Doc.AElstein]

Further…
_.. I think you get the point that the Temp() and Debug.Print stuff was just to get an initial formula similar to that I need. It was just for convenience as you can copy easily from the Immediate Window, which I tend to often have open anyway.
As I mentioned before you could do away with that section and just get the formulas from the formula bar, or as you obviously know them well you could write them off the top of your head anyway.

_ If you do use the Temp() and Debug.Print stuff to get a row of formulas then note

For my code snippet I had a returned Array of 1 “row” and 4 columns ( F to I ) . ( 1 To 1 , 1 To 4 )

Hence to get each Array member Debug.Printed out I had

Debug.Print Temp(1, 1)
Debug.Print Temp(1, 2)
Debug.Print Temp(1, 3)
Debug.Print Temp(1, 4)

_......


If for example you are looking at 3 Cells in a row , say F to H, then you would get a returned Array of 1 “row” and 3 columns ( F to H ) . ( 1 To 1 , 1 To 3 )
Correspondingly you would do
Debug.Print Temp(1, 1)
Debug.Print Temp(1, 2)
Debug.Print Temp(1, 3)


-
_.....

For a single Cell case, I think you see now it must be done a bit differently ( if you want to use the Immediate window to copy a formula from )