I have cells in a row with formulas that have the number 25 in them, such as, for example for two of them: =Project1!E25+... and =Project2!G25-....

I need to go to the cell and edit out everything that appears after (to the right of) the 25, then accept the revised formula as is. It's never more than, say, 20 more characters to the right, in case that matters. Can someone (carefully, please) write me a macro that will do this on the current cell, then move one cell to the right and repeat until it gets to the last column in that same row?

Thanks so much!
Dean

I'd try something like this:

Dim strFormula As String

Do Until ActiveCell.Value = ""
strFormula = ActiveCell.Formula
strFormula = Mid(strFormula, 1, InStr(1, strFormula, "25") + 1)
ActiveCell.Formula = strFormula
ActiveCell.Offset(0, 1).Select
Loop

Wow, that was awesome!

Unfcfortunately, after doing a lot of custom edit replace setup stuff in a
worksheet, someone changed the specs on me, and so I am trying to find
quicker ways, via macro, to deal with this! And so I ask: One more please?

In the same row, as I move one cell to the right, across 12 * 20 = 240
columns from column L through IQ, all references are currently to the
Project 1 worksheet, but I would like it to be to Project 1 (in column L),
then project 2 (in column M), all the way through Project 20 (in column AE).
Then in the next column, column AF, I would like it to start with Project 1
again, then repeat for another block of 20 columns. Repeat until we've done
this a total of 12 times.

Hopefully, I can adapt this and the one you've given me earlier to do any
more by myself!

Thanks so much!
Dean


wrote in message
oups.com...
I'd try something like this:

Dim strFormula As String

Do Until ActiveCell.Value = ""
strFormula = ActiveCell.Formula
strFormula = Mid(strFormula, 1, InStr(1, strFormula, "25") + 1)
ActiveCell.Formula = strFormula
ActiveCell.Offset(0, 1).Select
Loop

I got this solved now, thanks to JMB, so no need to do anything more. Thx.

Dean

"Dean" wrote in message
...
Wow, that was awesome!

Unfcfortunately, after doing a lot of custom edit replace setup stuff in a
worksheet, someone changed the specs on me, and so I am trying to find
quicker ways, via macro, to deal with this! And so I ask: One more
please?

In the same row, as I move one cell to the right, across 12 * 20 = 240
columns from column L through IQ, all references are currently to the
Project 1 worksheet, but I would like it to be to Project 1 (in column L),
then project 2 (in column M), all the way through Project 20 (in column
AE). Then in the next column, column AF, I would like it to start with
Project 1 again, then repeat for another block of 20 columns. Repeat
until we've done this a total of 12 times.

Hopefully, I can adapt this and the one you've given me earlier to do any
more by myself!

Thanks so much!
Dean


wrote in message
oups.com...
I'd try something like this:

Dim strFormula As String

Do Until ActiveCell.Value = ""
strFormula = ActiveCell.Formula
strFormula = Mid(strFormula, 1, InStr(1, strFormula, "25") + 1)
ActiveCell.Formula = strFormula
ActiveCell.Offset(0, 1).Select
Loop