Macro help please

[Dean]

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

 

[Guest]

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]

This seems to work nicely, but doesn't seem to do what I asked for (and I
thought I was clear)! It looks like it changed the 2nd 20 cells to Project
2, the next 20 to Project 3. What I want is the first 20 cells to be
Project 1 through Project 20, then repeat for the next 20, all over again.

Also, it looks like you have an IIF in there, is that some sort of an
integer IF statement (or a typo)?

Thanks!
Dean

 

[Guest]

You were clear, I just got off track.

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 Mod 20) + _
IIf(i Mod 20 = 0, 20, 0), 1, -1, vbTextCompare)
Next i
End With

End Sub

 

[Dean]

I think it's still a little off. I had to terminate (so I don't know what it
might have done right first but, at some point), because it was looking for
worksheets Project 60, 70,..., then 61,71,....

I just want 1 thru 20, then repeat.

Thx
Dean

 

[Guest]

That's exactly what it did on my machine when I ran it (1 through 20, then
repeat). You're sure the code is the same as what's posted and there are no
extra 0's (for example, i mod 200 would give the results you're seeing).


Alternatively, using row 1 that has the numbers in them:

Sub test()
Dim rngData As Range
Dim rngCell As Range

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

For Each rngCell In rngData.Cells
With rngCell
.Formula = Replace(.Formula, _
"Project1", "Project" & _
.Parent.Cells(1, rngCell.Column).Value, _
1, -1, vbTextCompare)
End With
Next rngCell

End Sub

 

[Dean]

It does work and I can't fathom why it didn't last time. The only thing I
can think is that I ran your fixed macro on the file that I had messed up
after running the first incorrect macro on it, though I swear I got a fresh
copy. That's gotta be it.

Anyway, thanks much!

Dean