Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro in Office 2003

    Can anyone help please - this particular problem is driving me crazy:

    I want to take the output from a formula, paste it in a different column, recalculate the formula, copy the output and paste it in the cell beneath the previous paste. I did similar in Office 2002 with no problem. However, in 2003, when I record the Macro and use the right arrow, end to get to the top of the column I want to paste in then use down, end, down to get the next blank cell for pasting - it works ok when typing and when recording the macro but on execution it always goes to the last cell I filled prior to writing the macro, never the next blank cell?

    My macro looks like this:

    Sub Macro6()
    '
    ' Macro6 Macro
    ' Macro recorded 09/12/2003 by Thomas Conroy
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Calculate
    Selection.End(xlToLeft).Select
    Selection.Copy
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Range("G5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.End(xlUp).Select
    End Sub


    Its the "Range G5" that appears to be wrong, I want this line to read the equivalent of :

    "Move down 1 cell" which would be the new blank cell for pasting the reult from the calculated formula.

    What am I doing wrong?

    Best regards,

    Tommy Conroy.

  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 in Office 2003

    <P ID="edit" class=small>(Edited by sdckapr on 09-Dec-03 10:08. Added alternate code snippet)</P>You want the offset method.

    Is this what you are looking for in code?


    <pre>Sub Macro6()
    Calculate
    Selection.End(xlToLeft).Copy
    Selection.End(xlDown).Offset(1, 0).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    End Sub</pre>


    This will also do the same without doing any copying and pasting:
    <pre>Sub Macro7()
    Calculate
    With Selection
    .End(xlDown).Offset(1, 0).Value = _
    .End(xlToLeft).Value
    End With
    End Sub</pre>


    Steve

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro in Office 2003

    Hi and Thanks!

    I have tried the first solution that you offered and it works a treat.
    After I get my output finised I will try your simpler suggestion.

    Can you suggest how II might go about starting to learn this language, I can only record a macro and therefore do no understand how to write them from 1st principles?

    Once again, sincere thanks for your help.

    Best regards,

    Tommy Conroy.

  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro in Office 2003

    Hi and Thanks!

    I have tried the first solution that you offered and it works a treat.
    After I get my output finised I will try your simpler suggestion.

    Can you suggest how II might go about starting to learn this language, I can only record a macro and therefore do no understand how to write them from 1st principles?

    Once again, sincere thanks for your help.

    Best regards,

    Tommy Conroy.

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro in Office 2003

    Once again, many thanks for your help.

    I will have to start at the beginning but nevertheless its the only way!

    Best regards,

    Tommy.

  6. #6
    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 in Office 2003

    <P ID="edit" class=small>(Edited by Leif on 13-Mar-04 16:29. to update link to http://www.bmsltd.ie/)</P>Check code generated on this site by others (ask questions)
    Get a book on excel/VB programming (John Walkenbach has some good ones)
    Look at online tutorials

    Here are some links (excel and Excel VBA), that I have especially found useful:
    This is "Daily excel tips postings", an unmoderated board for Q&A where I also answer questions (when not answering them here)

    Here you can subscribe to weeklyexcel tips (comes on Saturday mornings)

    Here is Chip Pearson's topics (if you go to the bottom, you can see the search). Good Excel "primers" on many subjects.

    John Walkenbach's spreadsheet page is here

    Jon Peltier's Chart Tricks. He has some impressive tricks for manipulating charts and making some very interesting ones.

    The Excel MVP page.

    VBA
    MS article with links to some "How To"s

    MS excerpted chapters from programming books

    Some MS MVP articles: Using Recorder and VBA Basics

    Look here under "spreadsheet", plenty of good "articles".


    Steve

Posting Permissions

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