Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-03-2014, 07:56 AM
s7y s7y is offline Divide Number between cells Windows 7 32bit Divide Number between cells Office 2010 32bit
Advanced Beginner
Divide Number between cells
 
Join Date: May 2012
Posts: 38
s7y is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-15-2014, 12:58 PM
BobBridges's Avatar
BobBridges BobBridges is offline Divide Number between cells Windows 7 64bit Divide Number between cells Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 01-15-2014, 12:59 PM
BobBridges's Avatar
BobBridges BobBridges is offline Divide Number between cells Windows 7 64bit Divide Number between cells Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Divide Number between cells 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
Divide Number between cells 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:08 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft