Auto Insert Parenthesis
 

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.

Auto Insert Parenthesis
 

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

On Mon, 11 Jan 2010 07:10:01 -0800, Deb
wrote:

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.

Auto Insert Parenthesis
 

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" wrote:

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

On Mon, 11 Jan 2010 07:10:01 -0800, Deb
wrote:

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.

.

Auto Insert Parenthesis
 

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

On Tue, 12 Jan 2010 06:22:01 -0800, Deb
wrote:

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" wrote:

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

On Mon, 11 Jan 2010 07:10:01 -0800, Deb
wrote:

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.

.