# Macro Command to MOVE in Excel



## Lareux (Dec 5, 2004)

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


----------



## Jimmy the Hand (Jul 28, 2006)

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.


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


----------



## Lareux (Dec 5, 2004)

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 . :up: THANK YOU:up:


----------



## Jimmy the Hand (Jul 28, 2006)

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


----------

