Hi All - I have a spreadsheet table containing around 7000 rows of data. Each row contains details about a Healthcare worker and a visit they have undertaken to a Client. For each of these visits (rows) I need to calculate their respective rate of pay. Various criteria define what pay rate is applicable - such as County, weekday or weekend, Banks holiday etc. The module I am running works perfectly up until the point where it has to write the data into the table. I am running a loop sequence which checks all the relevant information on each row and then evaluates what rate is applicable. If I run the module without trying to write the data back to the table it runs in under a second. However, as soon as I introduce the following line of code as the last line in the loop, the run time becomes minutes rather than fractions of a second.
Range("M" & n).Value = rate * Range("E" & n).value Where `M' is the column reference of where I am putting the data within the table. `n' is the variable used in the loop. `rate' is the variable which is calculated for each row as being the hourly rate to be paid. `E' is a time expressed as a fraction (ie. 30 mins would become 0.5) I have done all the obvious things like turning off screen updating etc but just cannot work out why it runs so slowly as soon as I want to add this final line. I am hoping there is an obvious solution. At present, I haven't added the entire sheet and module to this post for Data Protection reasons. I am fairly new to writing VBA code, so I can only assume that either the line of code above is causing the problem or there is something about using VBA to write data into a table that I don't yet understand. Any help or light you can throw on the problem would be really helpful.
Range("M" & n).Value = rate * Range("E" & n).value Where `M' is the column reference of where I am putting the data within the table. `n' is the variable used in the loop. `rate' is the variable which is calculated for each row as being the hourly rate to be paid. `E' is a time expressed as a fraction (ie. 30 mins would become 0.5) I have done all the obvious things like turning off screen updating etc but just cannot work out why it runs so slowly as soon as I want to add this final line. I am hoping there is an obvious solution. At present, I haven't added the entire sheet and module to this post for Data Protection reasons. I am fairly new to writing VBA code, so I can only assume that either the line of code above is causing the problem or there is something about using VBA to write data into a table that I don't yet understand. Any help or light you can throw on the problem would be really helpful.
