Windows Forum·65,766 discussions

How can I Create a Macro with Absolute A

Locked

Here’s what I’m trying to do. I have created a macro that will calculate depreciation for a piece of equipment over a 60-month period. The macro auto-fills 60 cells (in a column -ie: F8:F67) with each months depreciation amount (and remaining residual value in the adjacent column) based on the value, in this case, entered in cell G7. The macro will Always be run in the cell located 1 down diagonally to the left of the cell that the equipment value is entered in. This works fine, unless I run the macro from a different cell, say I14:I73, which should then calculate based on the value in cell J13. It will return an error because the macro has an absolute reference to the cell range F8: F67. How can I tell the macro to just auto fill 60 cells starting from the ACTIVE cell?

Thanks in advance!

Topic

How can I Create a Macro with Absolute A

In reply to How can I Create a Macro with Absolute A

I’m not an expert on macros, but I do very good wild guessing 🙂

Does the F8:F67 reference appear as “$F$8:$F$67”?

If so, remove the ‘$’ characters–they indicate the absolute reference.

How can I Create a Macro with Absolute A

In reply to How can I Create a Macro with Absolute A

How can I Create a Macro with Absolute A

In reply to How can I Create a Macro with Absolute A

Hi!
1. Make the makro using relative values (there is a checkbox in the makro options for that)
2. Go into the makro code and there you find something like “move(0:1)”
Locate the Values and Calculations, that need absolute values and reference there to the absolute position of cells (ActiveSheet.Cells(1,1).value – look in your makro, that you are using right now)
Now a part of your macro uses relative positions (based on the active cell) and some calculations will be allways using absolute posoitions (like in your question)
hope that helps (email me, if you need more infos about that)
oliver

How can I Create a Macro with Absolute A

In reply to How can I Create a Macro with Absolute A

I emailed Oliver the workbook and he quickly replied with the answer. Thanks again Oliver!

How can I Create a Macro with Absolute A

In reply to How can I Create a Macro with Absolute A

This should be close to what you want. I recorded it in Excel2000 using relative references so you get all the funky formulas you need … Enjoy!

Sub Macro5()
‘
‘ Macro5 Macro
‘ Macro recorded 6/26/2000 by Jim Rau
‘
‘ This will calculate 60 rows of depreciation and format the depreciation figure
‘ as a percent and the remaining numbers as currency

‘ the cell with the starting value is selected and the percentage depreciation
‘ is right above that … this assumes linear depreciation
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “=RC[-1]*R1C7”
ActiveCell.Offset(1, -1).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “=R[-1]C-R[-1]C[1]”
ActiveCell.Offset(-1, 1).Range(“A1”).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range(“A1”).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range(“A1:B1”).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(1, 0).Range(“A1:A58”).Select
ActiveSheet.Paste

How can I Create a Macro with Absolute A

In reply to How can I Create a Macro with Absolute A

Jim was working on this for me, but received the answer from Oliver first. Thank you for trying Jim.

How can I Create a Macro with Absolute A

In reply to How can I Create a Macro with Absolute A

I guess it ran a bit long! Here is the rest of the macro. And as long as I have room I will share the details …

Position your cell on the starting point, select Tools then Macros then record new macro. In Excel97 you choose the relative references, in Excel2000 you click a button. This is what you need to get a macro than runs from your starting point (wherever it is) on.

ActiveSheet.Paste
ActiveCell.Offset(-3, 0).Range(“A1”).Select
Application.CutCopyMode = False
Selection.NumberFormat = “0.00%”
ActiveCell.Offset(1, 0).Range(“A1:B60”).Select
Selection.NumberFormat = “$#,##0.00”
ActiveCell.Select
End Sub

Good luck with your thing …

How can I Create a Macro with Absolute A

In reply to How can I Create a Macro with Absolute A

[Author]

Replies