dividing data equally between cells VBA

hello everybody.. I need a VBA code to do the following:

in sheet 1 I have a range J8:J57. Here I have some numbers that I need to be equally divided into cells (in columns) in sheet 2. Right now I am using the following formula: =ROUND(sheet1!$J$9/96;14). I then pull this formula down 96 cells to have the result. Then I move on to the next cell and do the same with the following formula: =ROUND(sheet1!$J$10/96;14).

This takes forever and, most important, is bound to create a problem if one of my colleagues deletes the formula from the cell.

Is it possible to have VBA to do the job by selecting the cell and its destination range (ranges can vary between 96 and 125 cells)...?

Also, is it possible to have the function update the destination cells whenever a change is made on sheet1?

Hello xladept,

attached please find a sample file. On sheet1 you can see some data the way it is presented. Sheet 2 is where all happens. Right now I use the "round" formula. I would like to have VBA to do that. Please keep in mind that the number of cells, on sheet2) that need to have the data may vary from 90 to 150. This variation is not sequential (like 90, 91, 92 and so on)

Hello bsalv, thanks for the reply. when I run your macro I see that numbers are placed also in a horizontal way. As you can see from my sample sheet this is not the case as the results are always vertical. And, also, how do I get the macro to automatically run when there are changes in any of the source cells?

Hi XLAdept, it is a know data:

the cells in sheet2 are CJ and CK

268 to 363 (96)
365 to 488 (124)
490 to 614 (125)
616 to 740 (125)

and so on....

please note that CJ and CK fetch data from different cells

see attachment

it's chinese

Hi bsalv, this looks almost like the code I need... let me try to explain exactly what I need to achieve with the new sheet.

Sheet1 first (weight) and second (loss) column is where I will insert the weights. This will, of course, vary, depending of what the warehouse prepares.

Sheet2 is where I need to result to appear. The amount of cells where I need the weights and the loss to be divided are always the same. For instance Sheet1! A2 and B2 will always be divided between 96 cells. Sheet1! A3 and B3 between 124 cells. And also, the positions in Sheet2 where the data will start is always the same (for example sheet1!A2 will always be divided between cells from A1:A95 on Sheet2)

I hope this makes more sense

i'm blonde what's your excuse ?

i added the number of cells in a 3rd column, because untill now i have no idea where i should find your numbers.