#1
|
|||
|
|||
Divide Number between cells
hello everybody.. and happy new year.. 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 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(sheet!$J$8/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? Last edited by s7y; 01-03-2014 at 08:06 AM. Reason: Add |
#2
|
||||
|
||||
S7y, I apologize; I didn't notice this before, and it's only by sheerest luck that I happened just now to glance down the list to look at old posts. I'm surprised no one else responded, but then it was just after the holidays; maybe they all missed it too.
I'm sure it's possible to have VBA do what you're talking about, but I'm not sure yet what you mean. I think (after rereading a couple times) you're saying something like this: For each cell in Sheet1!J8:J57 you want to create a column of n values in Sheet2, each value to be calculated as ROUND(x,14)/n, where n is anywhere from 96 to 125. So if n is 100 and in Sheet1!J8:J57 are the values 800, 900, 1000, 1100 and so on up to 5700, you want Sheet2 to have a series of columns (let's say H1 through BE100); in Sheet2!H1:H100 should be the value 8, in column I should be 9, in J 10 and so on. Is this even partly right? If so, yes, it's easy to get VBA to do it. It may even be possible to do without VBA, though I'd have to think about it. |
#3
|
||||
|
||||
Oh, and yes, it's also possible to have the VBA program update Sheet2 every time Sheet1 is changed...though thinking through the implications first may be a bit more complicated.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Hide Divide by Zero Option? | SteveBump | Excel | 4 | 12-15-2013 09:53 PM |
how to divide into group | tomlam | Excel Programming | 2 | 05-20-2013 09:46 PM |
divide page into 4 parts | aclark17 | Word | 4 | 01-17-2012 09:04 AM |
How to organize and insert blank cells between a huge number of data? | tareq | Excel | 12 | 09-29-2010 02:12 PM |
Divide by zero error | doublejoy | Excel | 1 | 08-22-2009 03:10 AM |