EXCEL - FIND AND REPLACE 2

[susejdrol]

Team,

I have a worksheet full of formulas that reference other sheets in the workbook, and I was going to have to delete and recreate some of other worksheets. So, I did a find and replace all to replace = with '= which made ever formula into a string.

After fixing the other worksheets I went to revert them back so I did a find and replace all to replace '= with =. However, it says that it cannot find any instances in the sheet.

Do any of you have any ideas on what I may need to do to convert them back.

Thanks

CP

 

[KenWright]

Use the following code which should do it:-

Sub Changeback()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If Left(c, 1) = "=" Then
c = Right(c, Len(c))
End If
Next c
End Sub

If you are not sure how to use a macro then just post back

Regards
Ken...............

 

[Zathras]

If you don't want your numbers to remain left-justified, you may prefer this slightly different version:
[blue]

Sub ReFormulate()
Dim c As Range
  For Each c In ActiveSheet.UsedRange
    If Not c.HasFormula Then
      If Left(c.Text, 1) = "=" Then
        c.Formula = c.Text
        c.HorizontalAlignment = xlGeneral
      End If
    End If
  Next c
End Sub

[/color]

 

[KenWright]

Hi Zathras, it didn't have a problem in 2002 but I never though to try in an earlier version. All my numbers ended up justified correctly.

Regards
Ken...............

 

[susejdrol]

Team,

Thank you very much for the assistance. I went ahead and used the second option just in case, and it worked perfect.

Thanks again.

CP