Macro help!! Almost there..

dlhirving

New Member
Joined
Oct 25, 2006
Messages
6
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!!!

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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
Code:
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

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
What is the actual objective of this macro? (Another words what is it supposed to be doing?)
 

dlhirving

New Member
Joined
Oct 25, 2006
Messages
6
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

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

New Member
Joined
Oct 25, 2006
Messages
6
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 -
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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

New Member
Joined
Oct 25, 2006
Messages
6
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,790
Messages
5,544,296
Members
410,601
Latest member
Silver2
Top