Subscript out of range error

[graemeal]

The first code is my original that works fine. Activated by CTL F

I want to remove one of the ranges and change the other one within the code EG

When I try to activate the bottom code by CTL F I get a Subscript out of range error

Thanks

From this

Range("J2:J1839").Formula = "=IF(AND(G2,D2>1000,C2>1000),D2/C2,0)"
Range("L2:L1839").Formula = "=IF(AND(G2,D2>1000,C2>1000),Y2/Z2,0)"

To

Range("J2:J1839").Formula = "=IF(AND(G2=0,J2>1.1,L2>1.2,D2>1000,C2>1000),J2+L2/2,0)"

Sub Macro5()
Windows("1876.xlsm").Activate
    Sheets("Sheet1").Select
    Range("J2:J1839").Formula = "=IF(AND(G2,D2>1000,C2>1000),D2/C2,0)"
    Range("L2:L1839").Formula = "=IF(AND(G2,D2>1000,C2>1000),Y2/Z2,0)"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A1839")
    Range("A2:A1839").Select
    Range("A2").Select
End Sub

To This

Sub Macro5()
Windows("1876.xlsm").Activate
    Sheets("Sheet1").Select
    Range("J2:J1839").Formula = "=IF(AND(G2=0,J2>1.1,L2>1.2,D2>1000,C2>1000),J2+L2/2,0)"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A1839")
    Range("A2:A1839").Select
    Range("A2").Select
End Sub

 

[Marcelo Branco]

Hi,

I think the formula is creating circular-references in column J.

M.

 

[graemeal]

Thanks but it works fine as a single formula in the sheet

 

[Norie]

Where in the code do you get the error?

Does this work?

Dim wb As Workbook
Dim ws As Worksheet

    Set wb = ThisWorkbook
 
    Set ws = wb.Sheets("Sheet1")
 
    With ws
        .Range("J2:J1839").Formula = "=IF(AND(G2=0,J2>1.1,L2>1.2,D2>1000,C2>1000),J2+L2/2,0)"
        .Range("A2").Value = "0"
        .Range("A2").AutoFill Destination:=Range("A2:A1839")
    End With

 

[graemeal]

It does not show where the error is. A box pops up say "Subscript out of range" and your options are to click OK or help. If you click OK the box disappears, if you click help it takes you to online help with the following comments which I have no idea what it means.

Maybe the formula is too long???. But as I said if you place it manually into a spreadsheet is works fine.

Thank you kindly

"You referenced a nonexistent array element.
The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the UBound and LBound functions to condition array accesses if you're working with arrays that are redimensioned. If the index is specified as a variable, check the spelling of the variable name."

 

[Norie]

There is nothing wrong with the formula other than possibly the circular reference Marcelo mentioned.

Are you sure there was no option to debug when you get the error?

Did you try the code I posted?

 

[graemeal]

Yes no option to debug

Yes I did try your code but get an error on the word "SET" on this line "Set wb = ThisWorkbook"

Excuse my ignorance but I assume that there are no spaces bewteen lines.

 

[Michael M]

To Highlight the code error;

Use Alt +F11 to open the VB Editor
Place the cursor in the code
Now press F8 to step through the code 1 line at a time.
When the code errors out it will be the line that is highlighted yellow.

 

[Norie]

What reference did you get on that line of code in what I posted?

If that really gave you an error there must be something else going on.

 

[graemeal]

If this is what you mean? Error says "Compile error" "Invalid outside procedure"

This is how the code looks. I have pasted into ThisWorkbook. I did change the Range to K which is what it should be.

The old code where I get the Subscript out of range. when I step through the code like Michael suggested (thank you Micheal) as soon as it enters "Windows("1876.xlsm").Activate" the error says "Run-time error '9'; Subscript out of range" but nothing is highlighted


Thank you kindly

Dim wb As Workbook
Dim ws As Worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    With ws
        .Range("K2:K1839").Formula = "=IF(AND(G2=0,J2>1.1,L2>1.2,D2>1000,C2>1000),J2+L2/2,0)"
        .Range("A2").Value = "0"
        .Range("A2").AutoFill Destination:=Range("A2:A1839")
    End With