Formulas to be transferred to VBA

easy thing you could try is to limit the range of your formulas.

for example on sheet 'Claims Check' you have a formula in cell U2

Please Login or Register  to view this content.

so this is looking at the whole of columns G and C. If you knew that data in column G was only likely to go down to row 1000 then you could change it to

Please Login or Register  to view this content.

but also that COUNTIF formula doesn't look right to me as the first part should refer to a range (which you have correct) but the second part should be looking at a specific cell or value (but you have it as a full column)

hope that helps

John

I understand what you are saying so I have changed the code to below:

Code:

=COUNTIF('g2 graded'!$G$1:$G$10000,C1)

however it is stil really slow. Is it possible to set up a VBA for formulas?

Thanks
Gemma

I understand what you are saying so I have changed the code to below:

Code:

PHP Code: 

=COUNTIF('g2 graded'!$G$1:$G$10000,C1) 


however it is stil really slow. Is it possible to set up a VBA for formulas?

Thanks
Gemma

is it slow when it is opening the s/s or when you're working IN it?

Would you mind posting a copy of your amended formulas sheet.

There is definitely a way to run this with VBA but I think things should be kept simple (with formulas etc..) unless you really need to go down the VBA route and I would just like another look at the sheet because it shouldn't be really slow

Thanks
John

It is quite hard to show you as my original workbook is 4000kb and I can only post up to 1000kb here.

I have attached the updated sheet and have also included some data on g2 graded sheet which is the type of data on around 8 sheets on my full workbook.

I am not sure if it may just be because of the sheer size of the file that it takes so long. When I tried to set it up again from fresh I started with the g2 graded sheet only. I put my data on and ran the macro and it took around 3 seconds to work. Then when I included the 'claims check' page with all the formulas it took around 3 times longer to work, which made me come the conclusion that it is the claims check formulas which is slowing it right down. on my full workbook it sometimes takes 1-2 minutes for my macros to complete once started, and also it is quite slow to load, though still only takes around 5 seconds to load.

As on a monthly basis there is around 10000 rows used up on 4 different sheets I have started exporting each months to mmicrosoft access then starting from fresh. I then import back and refresh the information when needed on my excel sheet.

Thanks

Gemma

Hi, I've created this code below but please make sure that you understand the following.

1. A button needs placing on the Claims Check with this macro behind it
2. Please look at the code and understand that you will need to edit the code if you add/remove columns as currently you will see that it looks at fixed columns.
3. Delete all the current formulas or when you run the macro it will be constantly trying to calculate those formulas and it will slow right down.
4. This will run down all the cells in column R (and stop when it reaches a non-blue cell). It will calculate values for the other columns as well.

Don't hesitate to come back to me if you have any problems

Please Login or Register  to view this content.

Thank you very much for this. I have went through it and understand it, but I can't seem to be able to manage to get it to work for me yet. I am sure it is something so simple that I havent done to get it to work.

I have copied the code and pasted by right clicking on claim checks and clicking on view code and pasting in there.

i have also inserted a button and assigned that macro to the button I also deleted all the formulas but for some reason it is not working.

I appreciated all your time and effort on this.

Thanks
Gemma

Hi, is there any error message or does it just do nothing? (Sorry for late reply - don't usually check on a weekend :-))

Hi. Thanks for reply.

It actually does nothing. I am not sure if it is something I am not doing.

Gemma

could you copy your Claims Check sheet into another workbook and post here please.

No problem. I have added horses in some C cell that are on the other workbooks so once I get the macro to work the cells for these horses should all have values in them.

Thanks again.

I've just ran the macro on that new workbook and it seemed to work fine. Are you sure you are running the macro when you're on the Claims Check sheet?

Thats very strange. Nothing happens when I press th formulas button on the claims sheet. I am using Excel 2007 and havent had any problems in the past with macros. So when you opened the new workbook I attached i you only press the fomla button on the claims sheet? And what came up once you pressed it?

Thanks
Gemma

I had added a macro button on sheet 4 and linked it with my macro for sheet 4 and when i click on this it works fine, but whenever i try and run the macro you kindly set up for me in sheet 5 nothing happens. im sure i am doing something very simple wrong.

Gemma

Hi, the macro is only setup to work when the 'active' (i.e. the current sheet you are in) sheet is the Claims Sheet. You will need to put the button on this sheet or the macro will need a fair bit of re-write

this is because when it is looking at cell A1 (for example) it is expecting A1 to be on the claims sheet.

im not sure where i am going wrong. as you can see by the last workbook i attached that i have the button with the macro you supplied linked to it on the claims check sheet, so when i press that button the active sheet is the claims check sheet but no formulas come up on that sheet.

I have identified the problem and it now works fantastic!

The issue was the colour index. I have changed it to Red 3 and it works like a dream.

Thanks again

Gemma

ahh yes, ive just noticed you had put the button on the correct sheet (i didn't move to the right enough!)

not sure how changing it to red makes it work (unless you have changed the colour on your sheet). But perhaps its a difference in our excel versions so if you're happy that its working then I'm happy!

glad I could help

John