Subscript out of range error

[Nelly2015]

Dont know what went wrong.

Sub AutoFormula2020()
Dim i As Long
ActiveWorkbook.Sheets("Overall").Cells(2, 2).Select
For i = 2 To 14 Step 1
Cells(2, i).Value = Cells(2, i).Value * -1
Cells(3, i).Value = Cells(3, i).Value * -1
Next i

End Sub

 

[Michael M]

Maybe this way, but I'm not sure if your looping rows or columns

Sub AutoFormula2020()
Dim i As Long
With Sheets("Overall")
    For i = 2 To 14 Step 1
        .Cells(i, 2)= .Cells(i, 2) * -1
    Next i
End With
End Sub

 

[jasonb75]

Subscript out of range on that code likely means that a sheet with that name doesn't exist in the active workbook.

edit:- I don't remember which error you would get if you tried selecting cells that are locked, so that may be something else to consider. Other than that, there is nothing in your code that would return the error that you are seeing.

 

[Zot]

Nothing is wrong.

When you have command
ActiveWorkbook.Sheets("Overall").Cells(2, 2).Select

and your current active workbook is not Overall, it will give error because the cells(2,2) cannot be selected since the sheet Overall has not yet selected. Program is dumb. You need to tell it step by step.

Avoid using select as much as possible. To address the cell just do something like this if you do not want to keep writing long Sheets("Overall") again and again when needed. You also no need to write Step 1 since it is default.

Sub AutoFormula2020()

Dim i As Long
Dim wsA As Worksheet

Set wsA = ActiveWorkbook.Sheets("Overall")

For i = 2 To 14
    With wsA
        .Cells(2, i).Value = .Cells(2, i).Value * -1
        .Cells(3, i).Value = .Cells(3, i).Value * -1
    End With
Next i

End Sub

 

[Nelly2015]

Subscript out of range on that code likely means that a sheet with that name doesn't exist in the active workbook.

edit:- I don't remember which error you would get if you tried selecting cells that are locked, so that may be something else to consider. Other than that, there is nothing in your code that would return the error that you are seeing.

I checked. The sheet is there.

You said the cells are locked. But my code doesn't lock any cell.

 

[Nelly2015]

Nothing is wrong.

When you have command
ActiveWorkbook.Sheets("Overall").Cells(2, 2).Select

and your current active workbook is not Overall, it will give error because the cells(2,2) cannot be selected since the sheet Overall has not yet selected. Program is dumb. You need to tell it step by step.

Avoid using select as much as possible. To address the cell just do something like this if you do not want to keep writing long Sheets("Overall") again and again when needed. You also no need to write Step 1 since it is default.

Sub AutoFormula2020()

Dim i As Long
Dim wsA As Worksheet

Set wsA = ActiveWorkbook.Sheets("Overall")

For i = 2 To 14
    With wsA
        .Cells(2, i).Value = .Cells(2, i).Value * -1
        .Cells(3, i).Value = .Cells(3, i).Value * -1
    End With
Next i

End Sub

It looped through a row. You can see from the code, cells(i,2), which means column B to M.

I tried your code. The same error appears.

 

[Nelly2015]

Nothing is wrong.

When you have command
ActiveWorkbook.Sheets("Overall").Cells(2, 2).Select

and your current active workbook is not Overall, it will give error because the cells(2,2) cannot be selected since the sheet Overall has not yet selected. Program is dumb. You need to tell it step by step.

Avoid using select as much as possible. To address the cell just do something like this if you do not want to keep writing long Sheets("Overall") again and again when needed. You also no need to write Step 1 since it is default.

Sub AutoFormula2020()

Dim i As Long
Dim wsA As Worksheet

Set wsA = ActiveWorkbook.Sheets("Overall")

For i = 2 To 14
    With wsA
        .Cells(2, i).Value = .Cells(2, i).Value * -1
        .Cells(3, i).Value = .Cells(3, i).Value * -1
    End With
Next i

End Sub

I tried your code. The same error appears.

 

[Zot]

It is B to N. I tested it and no such error. Must be something else about the sheet.

Check if you misspelled the sheet name.

 

[Nelly2015]

It is B to N. I tested it and no such error. Must be something else about the sheet.

Check if you misspelled the sheet name.

You are right. I just renamed the sheet with the same name. It worked. Actually they are the same. Wierd.
Thanks for your help!

 

[jasonb75]

You said the cells are locked. But my code doesn't lock any cell.

I didn't say that they were, I said it was something to consider. The cells could have been locked manually at some point, there was no indication either way in your post.

Actually they are the same.

Maybe there was a stray space at the beginning / end of the name. That is enough to make the code think that the sheet doesn't exist.