A
al007
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
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