• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculation through VBA

JAMIR

Member
Dear Sir,

Please see the file at https://www.dropbox.com/s/vxwwzxysqznjv8z/RIDA.xlsm?dl=0.

My data now 13000+ row and 100/150 row increased everyday. The above code is getting very long time to give balance stock of specific code no. enter. I have make pivot table for receipt control regiter & issue control register. For getting current stock of Unique Code No. i use following formula in Stock Available Colomn.
[=VLOOKUP(IssueControlRegister!E12537,Sheet1!$A$4:$B$2000,2,0)-VLOOKUP(IssueControlRegister!E12537,Sheet1!$C$4:$D$2000,2,0)]

Can it is speedy through vba code for getting current stock in issue control register Stock Available colomn after + - of specific code no.

Remember there is mass of data for handling this calculation. And all data in table so when i insert new row its taking so time for giving new row. I just want to speedup calculation


Please do needful


Regards,


Zameer
 
Have you tried in IssueControlRegister!L3:
=INDEX(Sheet1!$B$4:$B$1340,MATCH(E3,Sheet1!$A$4:$A$1340,0))-INDEX(Sheet1!$D$4:$D$1340,MATCH(E3,Sheet1!$C$4:$C$1340,0))
 
Hello Hui Sir,

Could you do this through VBA code.

Kindly do needful..

Warm Regards,

Zameer Shaikh
 
Hi Jamir. You've got a number of quite inefficient things going on in this file, which are causing your very slow recalculation.

Plus you’ve also got quite a few columns with formulas that don’t seem absolutely necessary. All these formulas cause Excel to slow down any time you make a change.

Furthermore, you have 3042 SUMIF formulas in the ItemMasterRegister table that are each pointing to a range in the IssueRegister that is 6500 rows long, and growing. SUMIF is a very resource intensive formula, and so having that many in the file pointing at a range that big is going to cause Excel to work very hard.

It's these SUMIFs that are probably causing the most delay, and there's probably a better way of doing whatever it is that these SUMIFS do, although I haven’t had a look at this bit in detail yet.

I do have a few observations about your file that I thought I'd share, for starters.

I see you have a column with nothing but =ROW() in it. If this column isn’t actually getting used for anything, I’d suggest you delete it, or maybe convert those formulas to values.

I also see you have a column in column D that does nothing but format the date using this:

=TEXT([@Date],"YYYY")&"-"&TEXT([@Date],"MM")

It would be more efficient to simply use a Custom Number format to do this, rather than a formula. In fact, is this really needed at all? All it’s doing is duplicating the data in Column C. If it’s not needed, delete it to free up some of Excel’s resources.

I also see you’ve got quite a few VLOOKUPS in the IssueRegister table. The VLOOKUPS in columns F and G that do almost the same thing: for any given row, they both look up things in the ItemMasterRegister based on the same CodeNo stored column E.

Likewisee, the VLOOKUPS in columns N and U do almost the same thing: for any given row they both look up things in the hidden UnitName table based on the exact same UnitCode stored in column M.

Most of the work involved in a VLOOKUP from Excel’s perspective is working out what row in the source table that the thing being lookup up lives. If you’re doing multiple VLOOKUPs on the same item – say a name like ‘Jeff Weir’ – in order to return say Jeff’s address in one column, and Jeff’s age in another, then under the covers we’ve made Excel work out what row in the Lookup table that the item ‘Jeff’ is on two separate times – once for each VLOOKUP.

Given this, it’s often more efficient to write a dedicated formula in a helper column to return just the row number associated with the ‘Jeff’ item. We can then reuse that row number to do both the Address lookup and the Age lookup using the much less resource intensive INDEX function.

I don't have time right now to look at your file further, but might get some time over the next few days. I have further ideas but they will have to wait till I get some more time.

If you email me on weir.jeff@gmail.com i can email a file back with the VLOOKUPS made more efficient for starters.
 
Hello jeff sir,

Thank for your reply. Fistly tell you that i have reduce my file with delete of necessary sheets and rows for uploading. Another date is helper column for pivot table. All columns are necessary & related to my job. But now a days there is mass of data and file is very slow response when i make changes.

Sir, i am working at automobile spare parts stores. And there is 5000+ items so i have created a little bit but very helpful application for my work. And this application is make possible only by this forum and by excel ninjas.

I am very thankful to all of you that reply to my any query. Sometimes i bother somebody. Sir i am giving you below link of my full data file.

https://www.dropbox.com/s/766a4h29riyzcm7/RIDAA.xlsm?dl=0

Thanks for your reply and giving you mail id. Your suggestion will appriated

Regards

Zameer
 
Hi again Zameer. I haven't downloaded your file yet, but I have had a look at your previous file.

Those SUMIFs formulas in the ItemMasterRegister table appear to give the exact same result as the 'Stock Available' column in the IssueRegister table.

So it seems to me that you shouldn't need to use two different ways to calculate the same thing.

The approach you are taking to calculate the Stock Available in that IssueRegister table is much more efficient than the approach in the ItemMasterRegister table where you use all those SUMIFS.

If I were you, I would delete all those SUMIF formulas, and instead do a similar lookup as you do for the IssueRegister table. That's assuming that you are even using that 'Stock Available' column.
 
Hello Jeff,

In itemmasterregister stock available column use for another purpose & In issuecontrolregister is used for data validation to stop user for issuing excess material or - balance. You are requested to please download my attached file.

And if it is possible with VBA code for getting current available stock at itemmasterregister & issuecontrolregister will be very helpful.

One thing i assume than it would be fast with VBA code. b'coz in issuecontrolregister when i insert new row it taking time to calculate for every column formulas. This time would be less through VBA.

Or can suggest that how i stop user to issue excess material than available quantity.

Regards,

Zameer
 
Jamir - I'm afraid I don't have time to help you out with this VBA request. All I can do is repeat my advice above, about fixing the underlying problem.

You have 3042 SUMIF formulas in the ItemMasterRegister table. Half of them point at a range that is 3000 rows long. The other half are pointing at a range that is 12500 rows long. This is very bad, because SUMIF is a very resource intensive formula. Get rid of those SUMIF formulas and use another approach.

The approach you are taking to calculate the Stock Available in that IssueRegister table is much more efficient. Use that approach in the ItemMasterRegister table too. Or rethink the layout of your entire setup.
 
Back
Top