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 <(E-Mail Removed)>
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 <(E-Mail Removed)>
>> 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.
>>
>> .
>>
|
Similar Threads
