1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro Command to MOVE in Excel

Discussion in 'Business Applications' started by Lareux, Mar 19, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Lareux

    Lareux Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    402
    I am trying to learn how to use macro's - Right now I am still on very basic stuff. This current macro I am trying to do is to MOVE Down 2 rows and Left 1 colum Then Type "Budget Amounts". Then I need to move down 1 type " Outstanding Income. The following macro does the trick UNTIL I have more rows in my sheet (Which will happen about every 3-4 times I need to run the macro). Can I MOVE instead of SELECT? If yes, How?

    Thank you.

    ' Macro1 Macro
    ' Macro recorded 3/14/2007 by me
    '

    '
    Range("F43").Select
    ActiveCell.FormulaR1C1 = "Budget Amounts"
    Range("F44").Select
    Selection.Font.Bold = False
    ActiveCell.FormulaR1C1 = "Outstanding Income"
    Range("F45").Select
    Selection.Font.Bold = False
    ActiveCell.FormulaR1C1 = "Outstanding Payments"
    Range("G47").Select
    ActiveCell.FormulaR1C1 = "=R[-6]C+R[-3]C-R[-2]C"
    Range("G44:G47").Select
    Range("G47").Activate
    With Selection.Font
    .Name = "Arial"
    .Size = 8
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    End Sub
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    Hi :)

    I suggest that you should use the Offset method.
    Activecell.Offset(2, 3) refers to the cell that is 2 rows down and 3 columns right from the active cell. Let's say Activecell is B3. Then Activecell.Offset(2, 3) refers to E4. See VBA Help for deeper understanding.

    I suppose you need something like this macro. Try it in action, and try to understand it's logic.

    Code:
    Sub Macro1
    ActiveCell.Value = "Budget Amounts"
    With Activecell.Offset(1, 0)
        .Font.Bold = False
        .Value = "Outstanding Income"
    End With
    With Activecell.Offset(2, 0)
        .Font.Bold = False
        .Value = "Outstanding Payments"
    End With
    Activecell.Offset(4, 1).FormulaR1C1 = "=R[-6]C+R[-3]C-R[-2]C"
    With Range(Activecell.Offset(0, 1), Activecell.Offset(4, 1)).Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    End Sub
    Jimmy
     
  3. Lareux

    Lareux Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    402
    That seems to work pretty well, but can I tell it to start out not changing the first cell? I would like to be able to start it at a total that is already calculated, and then have it move from there before changing anything. Is there a way to state that.

    By the way, sorry to be "Get stratight to business" with you. That is a wonderful piece of code, and it is very exciting:D . (y) THANK YOU(y)
     
  4. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    I say "MOVE" is not a perfect term to use. Of course, you can say "move", if you wish, as long as you are clear on what it means. The closest thing to "move" is when you Select the cells before writing data into them. In this case you can say that the selection moved from one cell to other. But in VBA you don't have to Select, you only need to refer to cells to give them values, change their other properties, etc.

    (Of course, the Macro Recorder always selects everything, but using Select just makes the code slower, and it rarely has any advantage at all.)

    Let's say the currently active cell is where the Totals are, where you want it to start. Now, ActiveCell.Offset(RowIndex, ColumnIndex) method can change the target reference so that it points on any desired cell. You only need to know the coordinates of the target cell, relative to the ActiveCell (i.e. the Totals). If the target cell to change is directly above the ActiveCell, then use ActiveCell.Offset(-1 ,0). If it's one step left, then use ActiveCell.Offset(0 ,-1), etc.

    Also, Offset method works with any range, not just ActiveCell, e.g. Range("B3").Offset(0, 4). If the Totals is always in the same cell, then maybe it's better to use Offset this way.

    So, what you want to achieve is, without any doubt, possible. No, it's not possible, it's easy. But as I don't know the layout of your worksheet, and I don't know the relative positions of the cells to change, I can't write the code for you. However, Offset method is not so difficult to understand and use, and I believe that you are able to write the code yourself.

    If you are still having problems, then please upload the workbook, so that I can see the layout and suggest the best solution.

    Jimmy
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/552967