Find and Replace Loop

[mrmandmman]

I have a vast number of cells which contain formulas.

These formulas have multiple cell references in them like this: Sheet1!G246

The numbers of the references are incorrect and need to be subtracted by 198. So instead of Sheet1!G246 the formula needs to have Sheet1!G48

The cell reference numbers range from x=246 to 1449, each one needs to be replaced with the number that is x=x-198. 246 needs to be 48 and 1449 needs to be 1251.

The columns are F:K and rows are from 253 through 1492

How do you write this in VBA?

Thank you!

[X10A]

Hi, this is what I managed to come up with. It took some time when I ran the code so I believe there is a better way to write this but it's all I can do for now.

You have to select the cells affected first. Then you run the code below.


Sub AmendRef()
Dim c As Range
For Each c In Selection
For x = 246 To 1449
c.Formula = Application.WorksheetFunction.Substitute(c.Formula, x, x - 198)
Next x
Next c
End Sub


Regards.

[nst1107]

This runs a lot faster, but may not be as precisely to your specifications as X10A'sOption Explicit
Sub a()
Dim cell As Range
Dim i As Integer, k As Integer
Dim flag As Boolean
For Each cell In Sheet1.Range("F253:K1492")
If cell.Formula = vbNullString Then GoTo skipit
flag = False
i = 1
Do
flag = Mid(cell.Formula, i, 1) Like "!"
i = i + 1
Loop Until flag
flag = False
Do
flag = Mid(cell.Formula, i, 1) Like "#"
i = i + 1
Loop Until flag
k = i
Do
flag = Mid(cell.Formula, k, 1) Like "#"
k = k + 1
Loop Until Not flag
cell.Formula = Left(cell.Formula, i - 2) & (Mid(cell.Formula, i - 1, k - i) - 198)
skipit:
Next
End Sub
Save a copy of your workbook before you run either!

[mrmandmman]

You really were not joking about how slow that is huh?

I ran the code from X10A because I understood it. It is working... just very slowly.

Thank you nst1107 and X10A!

[X10A]

Haha, told you it's slow. Most important, it works and that's good!

Please also take some time to mark the thread as solved.

Regards.

[david000]

What sorta speed difference are you guys talking about, and I would love to see an attachment sample to try these out?!