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

 

[Michael M]

The code in Post #10 works fine for me !!

Is the workbook you are referencing open ??

"Windows("1876.xlsm").Activate"

I think it should be

Workbooks("1876.xlsm").Activate

If it isn't open you will have to reference the file and path

 

[Norie]

The code should have Sub and End Sub, which I assumed you had.

Goto Insert>Module and add the code in the newly created module.

Sub Test()

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

End Sub

 

[graemeal]

Oh dear!!! I had saved the file name to 1877 so as not to mess up my original sheet and forgot about the

Windows("1876.xlsm").Activate

So sorry about all this

Apoligies again

Thank so much

 

[graemeal]

Thanks Norie. I am not very good at this. Thank you all again

 

[Michael M]

Ahh, glad you got it sorted....another victim of the Seniors Moment...LOL !!

 

[graemeal]

absolutely