Macro help!! Almost there..

[dlhirving]

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!!!

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

 

[HalfAce]

Hello dlhirving, welcome to the board.
I'm not sure if I understand everything you're trying to do here but see if this is anywhere close
and let us know what to change from there.

Sub bracket()

Dim UserRange As Range
Prompt = "Select a range for ...."
Title = "Select range"

On Error Resume Next
Set UserRange = Application.InputBox _
  (Prompt, Title, Selection.Address(0, 0), Type:=8)
Err.Clear
If UserRange Is Nothing Then
  MsgBox "Canceled."
Else
  For Each c In UserRange
    With c
      .Value = "(" & .Value & ")"
      .Font.Bold = True
    End With
  Next c
End If

End Sub

 

[Oorang]

What is the actual objective of this macro? (Another words what is it supposed to be doing?)

 

[dlhirving]

what this macro does...

hey, thanks for the reply,

after starting the macro, the user is instructed to select a range of cells they want bracketed. by bracketed, I mean going from Thursday to (Thursday)

the macro works like this:

1. takes the selected range
2. inserts a range of cells with the same rows, to the right
3. inserts the simple formula ="("&B4&")" and applies it to the range
4. copies those new bracketed cells and pastes (special) over the original range.

5. now I am left with a range (to the right) that I have to select and delete
=> this is where si am stumped....

thanks!

 

[HalfAce]

Have you tried the code posted above?
It simply adds a set of parentheses to the cell(s) in the UserRange - (and turns the font
bold 'cause that's what I thought you wanted to do but that can be easily removed if not.)

 

[dlhirving]

brackets -

yes, that is what I need it to do, but to get that done, I needed to insert a temporary ranges of cells, and now I want to get rid of them.
I am struggleing with selecting the range that needs to be deleted. this range is to the right of the range that was just bracketed and bolded.
I have tried a lot of offset stuff, but can't find the correct syntax.
The current range is UserRange - the range selected by the user at the srtart of the maco --

thanks again -

 

[dlhirving]

hey - if you want, I put the .xls here:
http://www.pedalmag.com/Courtenay_Results.xls

 

[HalfAce]

I'm still not sure if you've actually tried the code.
It puts the parentheses around the value(s) in the cell(s) themselves.
It never inserts any cells and therefore there's nothing to delete.

 

[dlhirving]

My apologies!
I guess I was caught in the lights -- and thinking too much like I was doing it in a worksheet and not in a macro.

thank you very much!

 

[HalfAce]

Most welcome. (Then I take it that was you were wanting to do?)