I'm looking for a standard macro which would replace the column letter
of cell conting formula as at times the formula may contains sheet name
letters which might be replaced causing problem
I've trid the macro below but does not seem to work. Pls help with a
macro which would work in multiple situations (with or without dollar
sign)Thxs
Sub Replacecolumnletter()
Dim c As Range
Dim Frm As String
Dim pos As Long
Dim Replacefrom
rng = InputBox("Letter to be replaced.")
Dim Replaceto
rng = InputBox("Letter to be replaced to.")
For Each c In Selection
pos = 1
Frm = c.Formula
Do Until pos = 0
pos = InStr(pos, Frm, Replacefrom
Select Case Mid(Frm, pos + 1, 1)
Case 0 To 9, "$"
Frm = Left(Frm, pos - 1) & Replace(Frm, Replacefrom, Replaceto
pos, 1)
pos = pos + 1
End Select
Loop
c.Formula = Frm
Next c
End Sub

One thing I note: You have Dim statements for Replacefrom and Replaceto but
you never set their values; instead you use rng for both inputbox results.
So the first inputbox result goes to rng, then rng immediately gets
overwritten by the second inputbox result. You never use rng in the code.

But beyond that I think there is an easier and faster way to do this:
Dim c as Range, i as Integer
Dim ReplaceFrom as String
Dim ReplaceTo as String

ReplaceFrom = InputBox("Letter to be replaced:")
ReplaceTo = InputBox("Letter to be replaced to:")

For Each c in Selection.SpecialCells(xlCellTypeFormulas)
With c
For i = 1 to 9
.Formula = Replace(.Formula, ReplaceFrom & i, ReplaceTo & i)
Next i
.Formula = Replace(.Formula, ReplaceFrom & "$", ReplaceTo & "$")
End With
Next c

I would also consider some way of validating the inputboxes to make sure
they give valid ranges, or put an error handler in here, since a simple typo
could make the code cause some pretty bad errors (suppose I type a number by
mistake as the "ReplaceTo" value - yikes!)
--
- K Dales


"al007" wrote:

I'm looking for a standard macro which would replace the column letter
of cell conting formula as at times the formula may contains sheet name
letters which might be replaced causing problem
I've trid the macro below but does not seem to work. Pls help with a
macro which would work in multiple situations (with or without dollar
sign)Thxs
Sub Replacecolumnletter()
Dim c As Range
Dim Frm As String
Dim pos As Long
Dim Replacefrom
rng = InputBox("Letter to be replaced.")
Dim Replaceto
rng = InputBox("Letter to be replaced to.")
For Each c In Selection
pos = 1
Frm = c.Formula
Do Until pos = 0
pos = InStr(pos, Frm, Replacefrom
Select Case Mid(Frm, pos + 1, 1)
Case 0 To 9, "$"
Frm = Left(Frm, pos - 1) & Replace(Frm, Replacefrom, Replaceto
pos, 1)
pos = pos + 1
End Select
Loop
c.Formula = Frm
Next c
End Sub

Dales,
I tried the macro & it doesn't seem to work - can you help.
Would like to add this code also ReplaceFrom & "!", ReplaceTo & "!" -
how can i do that.
thxs


K Dales wrote:
One thing I note: You have Dim statements for Replacefrom and Replaceto but
you never set their values; instead you use rng for both inputbox results.
So the first inputbox result goes to rng, then rng immediately gets
overwritten by the second inputbox result. You never use rng in the code.

But beyond that I think there is an easier and faster way to do this:
Dim c as Range, i as Integer
Dim ReplaceFrom as String
Dim ReplaceTo as String

ReplaceFrom = InputBox("Letter to be replaced:")
ReplaceTo = InputBox("Letter to be replaced to:")

For Each c in Selection.SpecialCells(xlCellTypeFormulas)
With c
For i = 1 to 9
.Formula = Replace(.Formula, ReplaceFrom & i, ReplaceTo & i)
Next i
.Formula = Replace(.Formula, )
End With
Next c

I would also consider some way of validating the inputboxes to make sure
they give valid ranges, or put an error handler in here, since a simple typo
could make the code cause some pretty bad errors (suppose I type a number by
mistake as the "ReplaceTo" value - yikes!)
--
- K Dales


"al007" wrote:

I'm looking for a standard macro which would replace the column letter
of cell conting formula as at times the formula may contains sheet name
letters which might be replaced causing problem
I've trid the macro below but does not seem to work. Pls help with a
macro which would work in multiple situations (with or without dollar
sign)Thxs
Sub Replacecolumnletter()
Dim c As Range
Dim Frm As String
Dim pos As Long
Dim Replacefrom
rng = InputBox("Letter to be replaced.")
Dim Replaceto
rng = InputBox("Letter to be replaced to.")
For Each c In Selection
pos = 1
Frm = c.Formula
Do Until pos = 0
pos = InStr(pos, Frm, Replacefrom
Select Case Mid(Frm, pos + 1, 1)
Case 0 To 9, "$"
Frm = Left(Frm, pos - 1) & Replace(Frm, Replacefrom, Replaceto
pos, 1)
pos = pos + 1
End Select
Loop
c.Formula = Frm
Next c
End Sub

Just noticed an omission on my part; sorry; should have been this:
Dim c as Range, i as Integer
Dim ReplaceFrom as String
Dim ReplaceTo as String

ReplaceFrom = InputBox("Letter to be replaced:")
ReplaceTo = InputBox("Letter to be replaced to:")

For Each c in Selection.SpecialCells(xlCellTypeFormulas)
With c
For i = 1 to 9
.Formula = Replace(.Formula, ReplaceFrom & i, ReplaceTo & i)
Next i
' I HAD FORGOTTEN TO COMPLETE THIS LINE:
.Formula = Replace(.Formula, ReplaceFrom & "$", ReplaceTo & "$")
' Now I will add the line to handle the !
.Formula = Replace(.Formula, ReplaceFrom & "!", ReplaceTo & "!")
End With
Next c

Hope I got it right this time!
--
- K Dales


"al007" wrote:

Dales,
I tried the macro & it doesn't seem to work - can you help.
Would like to add this code also ReplaceFrom & "!", ReplaceTo & "!" -
how can i do that.
thxs


K Dales wrote:
One thing I note: You have Dim statements for Replacefrom and Replaceto but
you never set their values; instead you use rng for both inputbox results.
So the first inputbox result goes to rng, then rng immediately gets
overwritten by the second inputbox result. You never use rng in the code.

But beyond that I think there is an easier and faster way to do this:
Dim c as Range, i as Integer
Dim ReplaceFrom as String
Dim ReplaceTo as String

ReplaceFrom = InputBox("Letter to be replaced:")
ReplaceTo = InputBox("Letter to be replaced to:")

For Each c in Selection.SpecialCells(xlCellTypeFormulas)
With c
For i = 1 to 9
.Formula = Replace(.Formula, ReplaceFrom & i, ReplaceTo & i)
Next i
.Formula = Replace(.Formula, )
End With
Next c

I would also consider some way of validating the inputboxes to make sure
they give valid ranges, or put an error handler in here, since a simple typo
could make the code cause some pretty bad errors (suppose I type a number by
mistake as the "ReplaceTo" value - yikes!)
--
- K Dales


"al007" wrote:

I'm looking for a standard macro which would replace the column letter
of cell conting formula as at times the formula may contains sheet name
letters which might be replaced causing problem
I've trid the macro below but does not seem to work. Pls help with a
macro which would work in multiple situations (with or without dollar
sign)Thxs
Sub Replacecolumnletter()
Dim c As Range
Dim Frm As String
Dim pos As Long
Dim Replacefrom
rng = InputBox("Letter to be replaced.")
Dim Replaceto
rng = InputBox("Letter to be replaced to.")
For Each c In Selection
pos = 1
Frm = c.Formula
Do Until pos = 0
pos = InStr(pos, Frm, Replacefrom
Select Case Mid(Frm, pos + 1, 1)
Case 0 To 9, "$"
Frm = Left(Frm, pos - 1) & Replace(Frm, Replacefrom, Replaceto
pos, 1)
pos = pos + 1
End Select
Loop
c.Formula = Frm
Next c
End Sub