Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
parenthesis | Excel Discussion (Misc queries) | |||
Need VBA script to auto-insert value upon row insert | Excel Worksheet Functions | |||
auto insert | Excel Discussion (Misc queries) | |||
Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions | |||
Auto Insert/Auto Formula? | Excel Worksheet Functions |