Macro help please
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 |
Macro help please
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 |
Macro help please
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 |
Macro help please
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 |
Macro help please
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 |
Macro help please
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 |
Macro help please
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 |
All times are GMT +1. The time now is 11:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com