PDA

View Full Version : Search and Replace Macro



danesrood
04-17-2010, 04:06 PM
Dear All

I have a SumProduct formula that I use in dozens of rows, each on about 15 worksheets and need to regularly do a search and replace through the formula in a range of cells selected by me at run time looking for a column letter say L in the following guises (L or =L or L$ and replacing the L with an M.

I would like to be able to able to put in the old and new column letters at the start.

I have been trying to write a piece of code to do the three parts in one go but I cannot seem to get the L$ element to work properly.

As ever my grateful thanks for any help that you can provide.

mdmackillop
04-17-2010, 04:13 PM
Can you post the formula?

danesrood
04-17-2010, 04:24 PM
This is a sample of the formula:
=RANK(G4,G$4:G$73,1)+SUMPRODUCT(--(G$4:G$73=G4),--(B$4:B$73< B4))

so in this example I need to change all of G's to say an H

mdmackillop
04-17-2010, 04:51 PM
This should work for the given formula, but not in all cases. It cannot be used if the Function names contain the same letter as the letter to be changed.


Option Explicit
Sub ChangeCol()
Dim Swap
Dim Fmla As String
Dim Cel As Range
Swap = Split(InputBox("Enter Column, Old, New" & vbCr & "e.g. 4,F,G"), ",")
For Each Cel In Columns(CLng(Swap(0))).SpecialCells(xlCellTypeFormulas)
Fmla = Application.Substitute(Cel.Formula, UCase(Swap(1)), UCase(Swap(2)))
Cel.Formula = Fmla
Next
End Sub

GTO
04-17-2010, 08:30 PM
Hi ALL,

I was thinking that searching the formula for an uppercase letter followed by a number, dollar sign, etc, might get around the problem Malcom mentioned as to replacing letters that could be in the function's name(s).

The checks on the input box returns would of course need adjusted for 2007+.


Option Explicit
Option Compare Binary

Sub exa1()
Dim _
rSelection As Range, _
rCell As Range, _
OldLetter As String, _
NewLetter As String

OldLetter = InputBox("Old Letter(s)?", vbNullString)
NewLetter = InputBox("New Letter(s)?", vbNullString)

If (Not OldLetter Like "[A-Z]" _
And Not OldLetter Like "[A-I][A-Z]") _
Or _
(Not NewLetter Like "[A-Z]" _
And Not NewLetter Like "[A-I][A-Z]") Then

MsgBox "Both Column Letters being looked for and replaced with must" & vbCrLf & _
"be in the range of ""A"" through ""IV"" - and must be entered in UPPERCASE.", _
vbCritical, vbNullString
Exit Sub
End If

Set rSelection = Selection
For Each rCell In rSelection
If rCell.HasFormula Then
rCell.Formula = ReplaceFormula(rCell.Formula, OldLetter, NewLetter)
End If
Next
End Sub

Function ReplaceFormula(CellString As String, LetterIn As String, LetterOut As String) As String
Dim _
rexMatches As Object, _
rexMatch As Object, _
sTemp As String, _
sLeft As String, _
sLeftOld As String, _
lPos As Long
Static REX As Object

'// Set a reference if needed, declaring as Static so we don't have to to //
'// create for ea cell. //
If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.IgnoreCase = False
End With
End If

With REX
'// As REX may still exist from updating another range, define pattern here.//
'// 'Rookie' level efforts as RegExp's, and not much with formulas, but I //
'// think (read: hope) this would cover? A match would be made up of //
'// two submatches, the first being the letter(s) and the second being a //
'// digit (1-9) OR the dollar sign OR colon OR closing parenthesis. //
.Pattern = "(" & LetterIn & ")([1-9]|\$|\:|\))"
If .Test(CellString) Then
sTemp = CellString
'// Get all the matches in a given formula. //
Set rexMatches = .Execute(sTemp)
lPos = 1
'// Work our way through the matches, taking a bite out of sTemp ea loop//
For Each rexMatch In rexMatches
'// Note: Replace is VBA Replace, NOT REgExp //
sTemp = Replace(sTemp, rexMatch, LetterOut & rexMatch.SubMatches(1), 1, 1)
lPos = InStrRev(sTemp, LetterOut & rexMatch.SubMatches(1))
sLeftOld = sLeft
sLeft = sLeft & Left(sTemp, lPos + Len(LetterOut & rexMatch.SubMatches(1)) - 1)
sTemp = Mid(sTemp, Len(sLeft) - Len(sLeftOld) + 1)
Next
ReplaceFormula = sLeft & sTemp
Else
ReplaceFormula = CellString
End If
End With
End Function

Not super well tested, but seems to work?

Hope that helps,

Mark

danesrood
04-19-2010, 01:52 AM
GTO & mdmackillop

Thank you so much for taking the time to look at my problem. Unfortunately I'm away from my 2003 system so I won't be able to check them out for a bit.

I have Office 2007 at home but had problems with GTO's. I assume that I just create a module and paste it in. If I do that the macro shows up in the list but the run option is greyed out with just create available.

Many thanks to you both and subject to any comments on the above I will get back to you as soon as possible when I get on to the 2003 system.

Steve

danesrood
04-28-2010, 02:20 AM
GTO

Sorry for the long delay but I'd forgotten that just after I had posted the question I was away from the office for a week or so.

Anyway I have run this through my tables using a number of different letters and it works fine other than a minor thing - the letters in the input boxes must be uppercase. Not a big issue but I suppose it would be helpful if that could be adjusted.

You say that the input boxes need to be changed for 2007, in what way?

In any event I am very appreciative and grateful for the time that both you and mdmackillop have spent on this problem.

My sincere thanks.

Steve

GTO
04-28-2010, 02:58 AM
...the letters in the input boxes must be uppercase. Not a big issue but I suppose it would be helpful if that could be adjusted.

You say that the input boxes need to be changed for 2007, in what way?...

Hi Steve,

Here is just the bit that I am taking a bit of a blind stab at, as I have no access to 2007. Hopefully I have it correct.


Sub j()
Dim OldLetter As String
Dim NewLetter As String

OldLetter = UCase(InputBox("Old Letter(s)?", vbNullString))
NewLetter = UCase(InputBox("New Letter(s)?", vbNullString))

If (Not OldLetter Like "[A-Z]" _
And Not OldLetter Like "[A-Z][A-Z]" _
And Not OldLetter Like "[A-X][A-Z][A-Z]") _
Or _
(Not NewLetter Like "[A-Z]" _
And Not NewLetter Like "[A-Z][A-Z]" _
And Not NewLetter Like "[A-X][A-Z][A-Z]") _
Or _
(OldLetter > "XFD" Or NewLetter > "XFD") Then

MsgBox "Blech!"

End If
End Sub

If I have this correct, it should insist upon one to three letters, and not allow past column XFD, which from the link I found, is the new last column?

Hope it works:creator:

Mark