this is my first 'custom' macro. and here is what I need it do do:
1)user prompted to select range of cells => works fine
2)macro then inserts cells to the right of the range => works fine
2)apply formula in those inserted cells to put brackets before and after cell contents => works fine
3)copy the new range and paste special (values) over the original range => works fine
4)I cannot select the inserted range to delete it and bring everything back together
--> I have tried a ton of offset stuff and Range stuff but I can't get them selected!!!!!!!
here is what I have -- any help would be awesome!!!
1)user prompted to select range of cells => works fine
2)macro then inserts cells to the right of the range => works fine
2)apply formula in those inserted cells to put brackets before and after cell contents => works fine
3)copy the new range and paste special (values) over the original range => works fine
4)I cannot select the inserted range to delete it and bring everything back together
--> I have tried a ton of offset stuff and Range stuff but I can't get them selected!!!!!!!
here is what I have -- any help would be awesome!!!
Code:
Sub bracket()
Dim UserRange As Range
Dim PasteRange As Range
Prompt = "Select a range for ...."
Title = "Select range"
' Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection
If UserRange Is Nothing Then
MsgBox "Canceled."
Else
UserRange.Select
Selection.Insert Shift:=xlToRight
For Each UserRange In Selection
UserRange.FormulaR1C1 = "=""(""&RC[1]&"")"""
Next
UserRange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
UserRange.Offset(3, 0).Select
Selection.Font.Bold = True
' this is where I need to select the inserted range so I can delete it!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Application.CutCopyMode = False
' Selection.Delete Shift:=xlToLeft
End If
End Sub