Client account Spreadsheet - Compile error - to large to need shorten code

The code is not complicated, but requires a couple of hours work. I do not know what the issue with the code is, but some thing obvious is change the variable name "Integer" in to "Long". Integers could only cope with just over 32k rows, so it might be you are running out of memory. Go in the code, CTRL plus H, replace all Integers in to Long

i tried that.

It says compile error Procedure to Large.

I dont mind paying someone to do it and show us how to add more in future for ourselves.

There are hundreds, if not thousands who are looking for a business in the net. I know a dozens people from this site. You can try Ozgrid site

any other sites?

Google, words like excel experts or excel gurus, you are spolied for with choices

It's pretty horrifying that people can get away with charging for code like that.

Undoubtedly it will need some debugging, but I think this does the same thing, and is scalable to larger numbers of sheets without a huge hassle.

Please Login or Register  to view this content.

Obviously this is untested, so do make plenty of backup copies of your exiting data and code before trying it out.It's pretty horrifying that people can get away with charging for code like that.

Undoubtedly it will need some debugging, but I think this does the same thing, and is scalable to larger numbers of sheets without a huge hassle.

[code]Sub Button2_Click()

Worksheets(

it says type mismatch.. I put values in all d12:bb12 but that didnt fix it..

I am not a programmer at all

Thanks for the positive feedback, but I've already spotted an error, which should be fixed in this version:

Please Login or Register  to view this content.

NewRow = Worksheets("input").Range("D12:BB12") - Runtime errer 13 - type mismatch error .

i put the starting values in D12-BB12 which i think is just so when it writes on the sheet next time it moves down a line to do the next.

Andrew,
Anyone can claim to be "Expert" nowdays, under the cover of the Net and charge people extortion amount of money. The accounting body, ACCA, runs daily courses and charges £1000 per day for simple excel task

Pepe,
Your post is likely directed at me. People generally ignore any plea from members, but listed to Moderators. How many times I have asked to use Code tags, but only to be ignored by OP and other members?

Now that an admin has requested a title change I shall, of course, comply with rule #7 and refrain from posting further advice until the request has been complied with.

Prior to that there's nothing in the rules to say I have to police titles.

Originally Posted by spazem

NewRow = Worksheets("input").Range("D12:BB12") - Runtime errer 13 - type mismatch error .

Sorry, it's a really simple fix, but I can't give you the answer until you comply with the request to re-title the thread. Instructions on how to do this are in Arlu1201's post.

i am trying to find out how to do it

did it i think

You did, thank you.

Now that we're square with the mods this line:

Please Login or Register  to view this content.

Which is right at the start of the code, needs to be changed to:

Please Login or Register  to view this content.

now it says Application defined or object defined error for this codeError 1004

Please Login or Register  to view this content.

See, I told you there'd be some debugging to do

Is there any chance you can let me have a copy of your workbook? It's going to be much quicker for me to be able to run the macro and debug it, rather than having to go through it line-by-line with you.

i pmessaged you. thanks

Originally Posted by arlu1201

Its against forum rules to PM files to any user of the forum.

Really? Then you should add that to the list of rules.

As it is I'm happy to confirm that no file has been PM'd to me.

i never knew u could pm a file....

I did pm a message. Am i not supposed to do that to? why have that function then?

Originally Posted by Pepe Le Mokko

It's already there, rule nr 4 - Has always been

Except that rule 4 does not, in any way, say that a file can't be PM'd to members (I'm not even sure you can PM files).

Given the state of the forum right now I'd expect the admins to have better things to do than apply wild redefinitions of rules to threads where people are, you know, trying to help posters with Excel problems.

Anyway, made-up rules aside, here's my revised code for this:

Please Login or Register  to view this content.

The original file was never PM'd to me (because that's against the imaginary rules and also impossible), so any mythical future people with the same problem, who desperately need to work out if their file is in the same format as the OP's, will have to do what I did and deduce the layout of the file from the code.

wow... it works nearlty perfect but still one problem.

When it writes to the sheet after you input it needs to be the other way round. When it credits a sheet it actually needs to go into the sheet as a - and vice versa.. becasue the sheets themselves need to balance against the cashbook sheet if you know what i mean. Can we just change from + to - when it writes on the sheet for a property.. then its perfect

this guy has gone out of his way to help me and all you guys cant stop giving him a hard time. Without him i would have been in nowhere land. Please let him help me in peace.. Has has potentially today saved our business 1k a year in fees to keep updating our spreadsheet. he is a hero !!!!!

Originally Posted by Pepe Le Mokko

the underlying idea of the rule is probably a bit hard for you to catch.

Well, I normally save ad hominem attacks until later in the evening, but if you're ready to go there now then that's fine.

Originally Posted by spazem

When it writes to the sheet after you input it needs to be the other way round. When it credits a sheet it actually needs to go into the sheet as a - and vice versa.. becasue the sheets themselves need to balance against the cashbook sheet if you know what i mean. Can we just change from + to - when it writes on the sheet for a property.. then its perfect

I'm still a bit in the dark as to the actual sheet format, but is this any closer?

Please Login or Register  to view this content.

It sys type mismatch for

Please Login or Register  to view this content.

Ah, I haven't got your workbook, so I just put numeric values into all of the cells that are being moved around.

On the P sheets values are written to columns B-H, if you just let me know which of those columns need to be multiplied by -1 then I'll sort it.

i believe the H coloum needs to be multiplied by -1

dont need to multiply it.. i found the problem. Whatever is going getting inputed into the G colum need to actually go in the F coloum and vice versa on the sheets. Thats the only problem.

Dont even worry mate.. I just swapped the name of my input credit and debt boxes around. This was it works out fine.. WORKING 100%
I just got to get used to entering them in different boxes. Shouldnt take long hehehehe..

thanks so much mate

such a superstar !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ok i still have a problem. Cause i inversed the entrys the sheets for each proeprty aer right but the cashbook sheet is wrong. The value in h has to be multiplied by -1. Thanks

So, just column H in the cashbook to be multiplied by -1.

This should do the trick...

Please Login or Register  to view this content.

i have no idea whats happened now. The first entry in the cashbook was perfect. I put in a credit of 3000 then when i put the second credit it actually minused it from total when it should have added it.

Here is what it looks like

CLIENT CASH BOOK
Trans Date Property Ref Details DR CR Balance
No Ref
1 31/07/2012 1 Test Flat Tenancy Deposit 3,000.00 3,000.00
2 01/08/2012 1 Test Flat Rent Received 2,166.67 -833.33


the second extry should have made it 5166.67. On the p1 sheet is right but not on this cashbook..

hope this makes sense to you. thanks again

Strange, as I'm pretty sure I've duplicated everything that was in the original code.

Anyway, give this a try:

Please Login or Register  to view this content.

Nice one!

You may want to edit your post, as you appear to have your actual sheet password in there