Auto Insert Parenthesis

  • Thread starter Thread starter Deb
  • Start date Start date
D

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.
 
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
 
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.
 
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
 
Back
Top