vba to speed up macro

Hello,

I have got a worksheet with 18 columns which I need to update from a different using the index and match formula in excel. The problem is that I use data from SAP which seems to be extremely difficult to be read by excel. During the calculations below on the sheet I can see something like Calculating 2processor(s)) --%" and it takes about 20min for excel to update all 18 columns with 10 000 rows. Is there any way I can use vba (for example) arrays to speed up the whole process? The actual formula which I use is the following:

Please Login or Register  to view this content.

The thread is open here too
http://www.mrexcel.com/forum/excel-q...ion-macro.html

Hi
Could you attach a sample of your file

Hello Nilem,

I would attach it but the workbook is just too large. When I put the data from SAP to Excel the workbook's size suddenly changes to about 15-20 mb although there are only 10 sheets in. And that's why it takes so long for the calculations to take place. I guess this is a SAP-Excel issue. I am not sure. I was just wondering whether I could you use the evaluate function in vba to speed up the calculations. For example from A7 till A10000 I want to use the formula above whereas the lookup value changes as we go down the rows. In other words I want to make the calculations out of the sheet (as an array in vba). Is that possible?

or you could make a small sample of your file, leaving a few rows in each sheet in order to calculation logic would be understandable

Hello,

Here I have simulated a tiny part of my sheets just principally to show you the worksheet formula. As I said as the data is complex it takes ages until all calculations are done so I will be happy to do that using vba and to be able to cut down the time it takes.

Thank you

Sorry it is exactly as on the sheet I have pasted. Ignore my comment before. I have copied and pasted the code without changing anything directly into the workbook test but it doesn't seem to do anything? Can you please double check?

Also, I was wondering whether we could update 9 other columns not just column C. For example columns F through N ?

OllieB I have just tested the macro on the same sheet and it does not work.

"The problem is that I use data from SAP which seems to be extremely difficult to be read by excel."
I had experienced this issue a few times in the past. My PC had crashed 3 times. Data exported from external application to tend to contain lots of unseen dirty data. Even if macro will not help lots. The simples solution is to copy values ONLY in to new sheet. Do not leave or work on data imported. You should first convert the data in to values only and delete the imported data. You will see a massive difference.

Another thing to think about is if you have access to MS Access, it may be easier to set up tables and queries there. When you are doing multiple selections, Access has the capability to set up those kind of relationships.

Oh well. I have pasted the data in another workbook using ctrl + V and reinputted the formula but it seems to be slow again. Don't know what to do.

ctrl + V is copy and paste. You need to highlight the entire sheet- click top right hand(Above A1).copy and then paste values ONLY, not just enter which tends to copy and paste all.

Brilliant OllieB. The macro works fantastic!! I have updated it. Thank you.