Auto Insert Parenthesis

[Deb]

How do I auto-insert parethesis around prepopulated data in a worksheet? I
have a column of information that only if there is information, then I would
like it to be in parenthesis. For example,

A1: data
B1: data
C1: no data
D1: no data
E1: data

I would like to auto-insert parenthesis around the cells that show "data"
but to ignore cells that have "no data". This would involve only 1 column in
the excel file. The cells that contain data would look like this, example:
(Smith) or (Jones) rather than Smith or Jones.

 

[Gord Dibben]

Sub parens()
Dim rng1 As Range
Set rng1 = ActiveSheet.Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng1
If cell.Value <> "" Then
cell.Value = "(" & cell.Value & ")"
End If
Next
End Sub



Gord Dibben MS Excel MVP

 

[Deb]

Thanks, Gord. This works excellent. Now if I could ask one more favor? How
can I get this to work in a specific column? The data I am trying to
manipulate is in column E. The current module wants to apply the info to
column A. If this is an impossible request, I think I know a way to move the
columns around so that the present module will work. Thank you so much.

 

[Gord Dibben]

You must change the column reference

See the pattern for (row, column)

Sub parens()
Dim rng1 As Range
Set rng1 = ActiveSheet.Range(Cells(1, 5), _ ' row1, column5
Cells(Rows.Count, 5).End(xlUp)) 'column5
For Each cell In rng1
If cell.Value <> "" Then
cell.Value = "(" & cell.Value & ")"
End If
Next
End Sub

This would also work.

Sub parens()
Dim rng1 As Range
Set rng1 = ActiveSheet.Range(Cells(1, "E"), _
Cells(Rows.Count, "E").End(xlUp))
For Each cell In rng1
If cell.Value <> "" Then
cell.Value = "(" & cell.Value & ")"
End If
Next
End Sub


Gord