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

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

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

"JMB" wrote in message
...
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

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" wrote:

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

"JMB" wrote in message
...
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

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


"JMB" wrote in message
...
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" wrote:

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

"JMB" wrote in message
...
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

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" wrote:

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


"JMB" wrote in message
...
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" wrote:

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

"JMB" wrote in message
...
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

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


"JMB" wrote in message
...
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" wrote:

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


"JMB" wrote in message
...
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" wrote:

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

"JMB" wrote in message
...
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