Replace C with A when C is followed by a number, but not otherwise?

E

Excel

I have =Income_Stmt!$c$1&Income_Stmt!c32 in a cell.
I want to write a macro that turns column C into column A.
I can't use search and replace because this would result in
=InAome_Stmt!$A$1&InAome_Stmt!A32.

Is there a way to do this?
 
R

Ron Rosenfeld

I have =Income_Stmt!$c$1&Income_Stmt!c32 in a cell.
I want to write a macro that turns column C into column A.
I can't use search and replace because this would result in
=InAome_Stmt!$A$1&InAome_Stmt!A32.

Is there a way to do this?
Click to expand...

for a macro, try this:

=====================
Sub CtoA()
Dim c As Range
Dim Frm As String
Dim pos As Long

For Each c In Selection
pos = 1
Frm = c.Formula

Do Until pos = 0
pos = InStr(pos, Frm, "C")

Select Case Mid(Frm, pos + 1, 1)
Case 0 To 9, "$"
Frm = Left(Frm, pos - 1) & Replace(Frm, "C", "A", pos, 1)
pos = pos + 1
End Select

Loop
c.Formula = Frm
Next c
End Sub
=======================


--ron
 
Top