Results 1 to 11 of 11

Thread: Macro's (2003)

  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Macro's (2003)

    Is there an easy way of editing Macro's. I know how to get into it, but I'm not sure what's going on. I recorded a Macro and want to add to it, rather than do the whole Macro again.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro's (2003)

    You are at the place to edit and it is as "easy" as changing the code <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    If you do not understand what the code does, you will have to much more specific about what you are want to do and what you have.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro's (2003)

    Hi Rob

    Need a little bit more information. There are two answers to this:

    1) Yes you can add to a macro if you hand code it

    2) No you cannot record a macro and then add to it by re-recording ( like a video tape for an analogy)

    If you cannot do #1 then you will have to re-record unless you want some help writing it. Hope that helps?
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro's (2003)

    I have a cell that I want to round off

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro's (2003)

    You can round off with a formula it does not take a macro.

    Could you attach the code?

    Steve

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro's (2003)

    I'm using a Macro because someone else is using the spreadsheet, and I want to make it as automated as possible. Here's the code:-
    Sub TextToNumber()
    '
    ' TextToNumber Macro
    ' Macro recorded 16/05/2006 by Rob Marshall
    '
    ' Keyboard Shortcut: Ctrl+n
    '
    Range("F3").Select
    Selection.Copy
    Range("E3:E30").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0%"
    Range("C3:C31").Select
    Range("C31").Activate
    ActiveCell.FormulaR1C1 = "=SUM(R[-28]C:R[-1]C)"
    Range("C3:C31").Select
    ActiveWindow.SmallScroll Down:=-3
    Range("B3:B31").Select
    Range("B31").Activate
    ActiveCell.FormulaR1C1 = "=SUM(R[-28]C:R[-1]C)"
    Range("B3:B31").Select
    ActiveWindow.SmallScroll Down:=15

    End Sub

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro's (2003)

    And what do you want it to do exactly after you edit it?

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro's (2003)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>I don't see any rounding at all in the macro?

    Steve

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro's (2003)

    No, because I don't know exactly how to enter the formula

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro's (2003)

    Rob,
    I think it might be easiest to ignore the existing macro altogether. If you can tell us what you are trying to achieve and which cells the relevant information is in, we can come up with a replacement macro for you.
    You might also post a copy of your workbook (zipped if necessary to get below the 100KB limit), censored to remove any confidential data.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro's (2003)

    Since we can't see what data you are operating on, the recorded macro doesn't tell us much - if I run it on a worksheet with a value in cell F3, and nothing else, I get the value of F3 copied to E3:E30 and formatted as a percentage, plus two sum formulas in cells B31 and C31. If you are looking to round the value in F3, plus the Sum formulas rounded, you could use this code as a replacement for all your code:

    Dim currCellF3 As Currency
    currCellF3 = Round(Range("F3").Value, 2)
    Range("E3:E30").Value = curCellF3
    Range("E3:E30").NumberFormat = "0%"
    Range("C31").FormulaR1C1 = "=ROUND(SUM(R[-28]C:R[-1]C),2)"
    Range("B31").FormulaR1C1 = "=ROUND(SUM(R[-28]C:R[-1]C),2)"
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •