batch edit cells

[willemeulen]

I have a bunch of cells which contain a reference but are missing the = sign
how can I add the = sign to complete reference in one go instead of one by one

 

[Mike H]

Hi,

To do it in the same cell requires code. Right click your sheet tab, view
code and paste the code below in and run it. Change the range to suit your
range

Sub adddequals()
Range("A1:a100").Value = Evaluate("""=""&A1:A100")
End Sub

Mike

 

[Dave Peterson]

I'd use a macro:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No text constants in this range"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
If .Len(.Value) > 0 Then
.NumberFormat = "General" 'or what you want
.Formula = "=" & .Value
End If
End With
Next myCell

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)