Hi Dave
Thanks for helping out - I see how you've shortened the code - it's
much better but unfortuneately your correct re overwriting my formula.
My starting ActiveCell is B4 & I've amended line 3 of the code to:
ActiveCell.Offset(-1, 3).Copy ActiveCell.Offset(, 2)
but it's just copying the formula for the balance - I need to be able
to edit this to multiply by 10% & divide by 12.
Any ideas
cheers
Peta
On Nov 30, 2:58 pm, Dave Bonallack <davebonall...@hotmail.com> wrote:
> Hi Pete,
>
> You can shorten your current code to:
>
> Worksheets("Sheet1").Activate
> ActiveCell = "Interest"
> ActiveCell.Offset(-1, 3).Copy ActiveCell.Offset(1, -1)
> ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]*0.1/12" 'need to make non absolute
> ActiveCell.Offset(rowOffset:=1, columnOffset:=-3).Activate 'next row Date
> column
>
> The macro recorder is very long-winded, and there are usually ways of tidyibg
> the code after recording a macro.
>
> The main thing about VBA is that you don't have to select the cell you want
> to work on.
>
> Also note my 3rd line. Range.Copy, followed by a space, then the destination
> Range.
>
> This also negates the need for the 'Application.CutCopyMode = False'
> statement.
>
> Also, note how the Offset function can shortened from what you had.
>
> It appears to me that you are writing your formula over the top of your paste.
>
> As to your formula, after I ran your code, it was relative, not absolute, so
> not sure what you mean by your question.
>
> BTW, if you use my shortened code, you may have to adjust the offset values
> in lines 4 and 5, since the ActiveCell stays constant.
>
> What is the original ActiveCell by the way?
>
> Regards - Dave.
>
>
>
>
>
> > Date: Sun, 29 Nov 2009 19:02:43 -0800
> > Subject: $$Excel-Macros$$ Re: Excel 2003 XP pro - how to reference an
> > adjacent cell in a formula
> > From: sq...@netspace.net.au
> > To: excel-macros@googlegroups.com
>
> > Please disregard previous post
>
> > Further to my post below I have been able to come up with the
> > following which calculates & inserts interest amount in D5. (Copies &
> > pastes the last balance & applies a formula)
>
> > 1.Worksheets("Sheet1").Activate
> > 2. ActiveCell.FormulaR1C1 = "Interest"
> > 3. ActiveCell.Offset(rowOffset:=-1, columnOffset:=3).Activate
> > 'last balance
> > 4. ActiveCell.Copy
> > 5. ActiveCell.Offset(rowOffset:=1, columnOffset:=-1).Activate
> > 6. ActiveSheet.Paste
> > 7. Application.CutCopyMode = False
> > 8. ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]*0.1/12" 'need to
> > make non absolute
> > 9. ActiveCell.Offset(rowOffset:=1, columnOffset:=-3).Activate 'next
> > row Date column
>
> > However I'm not sure how to modify the code (line 8) so that it can be
> > applied to the last balance row each month. Currently it keeps
> > referencing E3. From E4 down the column there is the formula =E3+D4-C4
> > (to deduct payments & add interest) (balance is a negative).
>
> > Sorry for the multiple posts.
>
> > Regards
> > Pete
>
> > On Nov 29, 7:23 pm, pete <sq...@netspace.net.au> wrote:
> > > Hi - i've got basic VBA knowledge & haven't been able to find how to
> > > select a cell relative to current cell - use offset?
>
> > > I'm setting up an loan statement
>
> > > 1 A B C D E
> > > 2 Date Details Dr Cr Balance
> > > 3 30/10/09 Opening balance $1000
> > 4. 30/11/09 Interest
>
> > > I have tried recording a macro to insert interest:
> > > 1. Sub Interest()
> > > 2. ActiveCell.FormulaR1C1 = "Interest" -/*always column B
> > > 3. Range("D4").Select
> > > 4. ActiveCell.FormulaR1C1 = "=R[-1]C5*0.1/12"
> > > 5. Range("B5").Select
> > > End Sub
>
> > > I want to do this every month & so thought I'd try automating it.
>
> > > I need to change line 3 to offset to column D on same line.
> > > Line 4 gets the last balance (column E line above current) & works out
> > > a months interest.
> > > I have a formula in E to add the interest from D.
>
> > > Thanks for the help
> > > Pete
>
> > --
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads
> > athttp://www.excelitems.com
> > 2. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com
> > 4. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
> > If you find any spam message in the group, please send an email to:
> > Ayush Jain @ jainayus...@gmail.com or
> > Ashish Jain @ 26may.1...@gmail.com
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,500 subscribers worldwide and receive many nice notes about
> > the learning and support from the group. Our goal is to have 10,000
> > subscribers by the end of 2009. Let friends and co-workers know they can
> > subscribe to group athttp://groups.google.com/group/excel-macros/subscribe
>
> _________________________________________________________________
> Looking to move this spring? With all the lastest places, searching has never
> been easier. Look now!http://clk.atdmt.com/NMN/go/157631292/direct/01/- Hide
> quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
