Try this, but be sure to back up your data in case it is not what you are
after. And, change the worksheet reference (I used Sheet2).

Sub test()
Dim rngData As Range
Dim i As Long

Set rngData = Sheets("Sheet2").Range("L12:IQ12") '<<< Change

With rngData
For i = 1 To .Cells.Count
.Cells(i).Formula = Replace(.Cells(i).Formula, _
"Project1", "Project" & (i \ 20) + _
IIf(i Mod 20 = 0, 0, 1), 1, -1, vbTextCompare)
Next i
End With

End Sub


"Dean" wrote:

Perhaps, someone can help me. Beginning in cell L12 (where I would place my cursor) I have a row with cells that all reference different cells on a worksheet called Project 1. The cells are fine but the references should not all be to the Project 1 worksheet.

As I move one cell to the right, I want to replace the reference to the Project 1 worksheet with a Project 2, then a 3... up to Project 20. Then, I want to continue on with the same procedure for the next 20 columns, until I have done this 12 times, for 240 total columns, which takes me all the way over to column IQ, which is just a few columns from the very last possible column.

In case it helps, in row 1, eleven row up above, from left to right, I have the correct replacement values, which is the integers 1 though 20, 12 times repeated. Just to be clear, in cell L12, the 1 would be replaced by a 1, in cell M12, the 1 would be replaced by a 2.

Can someone (carefully, please) write me a macro that will replace the 1?

Thanks much!

Dean