Hi Paul
Thanks a lot - what you've given works well & is much better than what
I had.
> but from there, you're jumping around the sheet.
> and copying info to the clipboard and pasting it.
> you don't need to do that.
This is also what Dave tried to point out, but I couldn't quite get it
to work.
Cheers & thanks again - also Dave
On Nov 30, 10:11 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> I've been trying to follow your posts (all) and am
> confused/disoriented/caffeine-deprived.
>
> I think you need to get rid of the use of "ActiveCell"
> or at least limit it's use.
>
> let's take a look at what your code is doing.
> first of all.
> line 2 puts the word "Interest" in whatever cell you
> currently have selected.
> .. ok, that works, as long as you have a cell in column "B" selected.
> but from there, you're jumping around the sheet.
> and copying info to the clipboard and pasting it.
> you don't need to do that.
> lines 3-6 seem to be an attempt to copy the contents of the cell
> one row above and 3 columns to the right of the current cell
> and place it in the current row, two columns to the right.
> (or, if starting in cell "B4", copy E3 to D4)
> Then, line 8 is OVERWRITING D4 with a formula...
>
> I think you should try:
>
> Dim R
> R = ActiveCell.Row
> Cells(R, 2).Value = "Interest"
> Cells(R, 4).FormulaR1C1 = "=Round(R[-1]C[1]*0.1/12,2)"
> Cells(R, 5).FormulaR1C1 = "=R[-1]C+RC[-1]-RC[-2]"
> Cells(R + 1, 2).Select
>
> (I added the Round function)
> and, I don't think you need the "Worksheets("Sheet1").Activate line,
> since if you aren't ON Sheet1 when you run the macro,
> you probably aren't going to be in the correct cell anyway!!!
>
> Is that what you're looking for?
>
> to make sure you don't have the wrong cell selected,
> you could use:
>
> If (activecell.column = 2) Then
>
> hope this helps,
>
> paul
>
> ________________________________
> From: pete <sq...@netspace.net.au>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Sun, November 29, 2009 10:02:43 PM
> Subject: $$Excel-Macros$$ Re: Excel 2003 XP pro - how to reference an
> adjacent cell in a formula
>
> 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-
> 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
