A
al007
I looking for a macro which would replace only the column letter in a
cell containing formula as at time there may me problem when cell
formula have letters which refer to sheet name.
Have tried the macro but it does not work - can anybody help or propose
an alternative.
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 replace 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
cell containing formula as at time there may me problem when cell
formula have letters which refer to sheet name.
Have tried the macro but it does not work - can anybody help or propose
an alternative.
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 replace 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