Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Macro Command to MOVE in Excel


(!)

Lareux's Avatar
Lareux Lareux is offline
Member with 402 posts.
THREAD STARTER
 
Join Date: Dec 2004
Experience: Intermediate
19-Mar-2007, 12:49 PM #1
Macro Command to MOVE in Excel
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's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
19-Mar-2007, 03:01 PM #2
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
Lareux's Avatar
Lareux Lareux is offline
Member with 402 posts.
THREAD STARTER
 
Join Date: Dec 2004
Experience: Intermediate
19-Mar-2007, 08:33 PM #3
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 . THANK YOU
Jimmy the Hand's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
20-Mar-2007, 02:16 AM #4
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

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2