Excel macro help please

Excel macro help please

Didn't find your answer?

I've created a cashbook excel worksheet for clients.  Rows 3 down to row 30 has formulas in them.   I've created a button and want to assign a macro that will insert say 10 cells at a time below row 30, that automatically include the formulas in the previous rows. 

My worksheet has 30 rows and foot totals at row 31, but the idea of the button/macro is so that the client can press it and he will have 10 additional rows, with formulas, so he can enter more transactions. 

Google is proving to be NOT my friend on this one.

Can anyone point me toward a code that will achieve this ?

Many many thanks

Replies (10)

Please login or register to join the discussion.

avatar
By ACDWebb
21st Feb 2013 22:21

Have you tried

recording a macro to achieve what you want using Relative rather than Absolute references and then editing the result?

Perhaps consider putting the totals at the top rather than the bottom, so running something like:

Row 1 - Totals as sum of the data belowRow 2 - Headings with the view frozen to always show rows 1 & 2Row 3 et seq - Input area with formulae.

That would save you having to move the foot totals each time you insert rows.

You can make the top row SUM() expand to cover new rows added  with a formula as below, which assumes that Column A will always contain a narrative entry

Sum for Column B:

=SUM(OFFSET(B2,0,0,COUNTA($A:$A),0))

If you are using Excel 2007 onwards you might consider formatting as a table. Then you can set the bottom row as a TOTALS row that expands as you add rows above, AND the cells that contain formulae should extend the formulae as you add rows.

Thanks (0)
avatar
By Manchester_man
21st Feb 2013 22:30

Thanks
Mamy thanks for your reply. I'm actually using excel 2010 so the tables option you suggest may be the best way.

I've never used a table before but assume I can keep all my other formatting ie drop down menus and the worksheet will otherwise appear the same as a normal non-table XLS?

Thanks again

Thanks (0)
avatar
By ACDWebb
21st Feb 2013 23:03

Yes

It even saves you having to freeze rows as the table header rows become the entries in the Row names (A:B:C) when you get down the page and they would normally disappear.

Give the table a recognisable name, if for example you are going to have more than one table in the workbook, then you can refer to it in formulae with the table name and referencing the header names for the table without having to create named ranges

So you might call your table tblCashBk and have the headers  DateNarrative : Amount

If elsewhere, outside the table, you wanted a cell to sum the amounts between 1 Jan 2013 & 10 Jan 2013 then you could use the formula

=SUMIFS(tblCashBk[Amount],tblCashBk[Date],">=01/01/2013",tblCashBk[Date],"<=10/01/2013")

Which makes reading your formula easier.

Your problem in sending the workbook to clients would be if they had a much older version of Excel that did not support Tables

Thanks (0)
avatar
By thacca
22nd Feb 2013 08:10

VT cashbook

have you thought about getting the clients to use VT cashbook?

Thanks (0)
avatar
By paulwakefield1
22nd Feb 2013 08:37

Tables is a good

option but be aware you cannot protect them easily and add rows*. You will probably need a macro that temporarily unprotects the sheet when a new row is added or else leave the sheet unprotected which, given that you have formuale, may be undesirable.

*Does anybody know whether this has changed in Excel 2013?

Thanks (0)
Replying to Eileen Searle:
avatar
By ACDWebb
22nd Feb 2013 09:08

Office 2013

paulwakefield1 wrote:

option but be aware you cannot protect them easily and add rows*. You will probably need a macro that temporarily unprotects the sheet when a new row is added or else leave the sheet unprotected which, given that you have formuale, may be undesirable.

*Does anybody know whether this has changed in Excel 2013?

I've not got that far having only downloaded 2013 last week and the dummy sheet I was tinkering with last night was not a protected sheet. Dragging down on the botton cormer of the table in an unprotected sheet adds rows easily. I shall have to have a further play over the w/e

The 2013 suite does seem a little flaky at times working with my Avast AV software. I have had to turn off Avast in Outlook as it just crashed every time I clicked send on an email :(

Thanks (0)
avatar
By User deleted
22nd Feb 2013 09:19

Is this cost/time effective ....

Sorry to put a damper on this thread, but with all the 'free' software about (desktop & Cloud),  why would you you spend chargeable time 'playing' at programming. Other 'professsional' developers have probably done it better and have a proven product

'playing' is used advisedly because we have all come across those whose primary discipline is not programming (accounting) - Furthermore, one only has to see the 'firestorm' on this site if an unqualified person makes a perfectly reasonable request for advice about starting up on their own as an accountant / bookkeeper etc. (there was a thread recently)

The fact that today’s software tools have removed a lot of the complexities of programming, doesn't mean that training can be ignored - and no a 'binary chop' is not something you have for dinner

Have you had training in aspect of programming or simply jumped straight in?What is your testing/release procedure?Have you written a proper user manual?What about update procedures over potentially many clients using your spread sheet - how do you roll out an update, update manuals, run a help desk etc..?How do you propose to audit/validate your work is correct and get a 3rd party to check it. Excel is notorious for howlers in this area?

.... and so on - these are just a few possible hurdles, but there are many more

At the end of the day if this is not your niche-market (presumably an accountant) then why bother? Yes it may be more fun than chargeable work but is that really a valid reason, and have you any concept of the possible work involved to properly maintain umpteen dispersed spreadsheets?

This is not to ignore the fact that there are some truly exceptional members of the profession who have a comprehensive grasp of both sides and produce impressive work, however, they are in the minority. Unfortunately the majority fall into the category of 'Excel jockey'

 

Thanks (0)
avatar
By paulwakefield1
22nd Feb 2013 09:53

It's horses for courses

There are lots of good free products out there (and VT Cashbook is one mentioned above) and certainly, if it is just a straightforward cashbook that is being developed especially if it is a generic version, an existing product may well be a better and more cost effective solution.

But there can be many reasons for developing something - normally a combination of a need for very specific tailored output, unusual inputs, ease of use and, to some extent, familiarity. Cross platform usage can also be a reason.

Your points are well made but be careful not to overstate it. Certainly it needs planning together with a logical and methodical train of thought; something accountants should be rather good at. I'm not suggesting that is sufficient to go out and write an accounting program or Apple app but there can be some very simple, very effective models that most people could write with a degree of knowledge and common sense.

I have models I wrote in Lotus 20 years ago which are still recognisably the same and still in use with a client.

Anyway as you say it is fun and it's good to create something for once. And who says it's not chargeable. :-)

 

Thanks (0)
avatar
By Manchester_man
22nd Feb 2013 10:57

Wow
Thanks for your input JC, a very concise post!

However, this is simply a 'basic' cashbook for the odd client. I have been asked by two clients if I can provide some kind of excel template to record incomings and outgoings and provide formulas for totalling and for agreeing closing balance to bank balance

I doubt whether I need to set up a 'help desk' and run update manuals!

I have a macro that (linked to a button) brings up a dialog box asking how many rows to add. That then copies the formulas but it only works from the active cell.

I have created a 'named cell' before the foot totals called 'end' and want to insert the rows there.

Just need to adjust the code which I'm sure I'll manage eventually but any help is appreciated.

The code I've used so far is

Sub InsertRow()
Dim Rng, n As Long, k As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required.")
If Rng = "" Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
'need To know how many formulas To copy down.
'Assumesfrom A over To last entry In row.
k = ActiveCell.Offset(-1, 0).Row
n = Cells(k, 256).End(xlToLeft).Column
Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown
End Sub

Ps thanks ACDwebb, I like the table idea but as you say, the 2 clients I have in mind are both on older excel versions.

Thanks (0)
avatar
By Manchester_man
22nd Feb 2013 11:53

Sorry posted twice
Posted twice

Thanks (0)