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
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 checked. The sheet is there.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.
It looped through a row. You can see from the code, cells(i,2), which means column B to M.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.
VBA Code: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.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.
VBA Code: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
You are right. I just renamed the sheet with the same name. It worked. Actually they are the same. Wierd.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.
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.You said the cells are locked. But my code doesn't lock any cell.
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.Actually they are the same.