Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusting formulas by copying across cells w/o changing the refere
Hello,
I was wondering if the below situation could be made easier: I have cells that already have a formula in them. The formulas have no specific pattern. Therefore I can't just adjust one formula and copy that across because the referenced cells will change. I need to adjust that formula for all the cells but the reference need to stay the same. For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3 I need to round the answers so I want the cell formulas to look like this: A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc... Is there anyway to add the "round" formula, or any formula for that matter, to one cell and then copy it to the remaining cells so that just the "round" formula is added and all the references stay exactly the same. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusting formulas by copying across cells w/o changing the refere
In a blank cell, type:
=ROUND(INDIRECT("A1"),-2) and paste across. "Jamie" wrote: Hello, I was wondering if the below situation could be made easier: I have cells that already have a formula in them. The formulas have no specific pattern. Therefore I can't just adjust one formula and copy that across because the referenced cells will change. I need to adjust that formula for all the cells but the reference need to stay the same. For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3 I need to round the answers so I want the cell formulas to look like this: A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc... Is there anyway to add the "round" formula, or any formula for that matter, to one cell and then copy it to the remaining cells so that just the "round" formula is added and all the references stay exactly the same. Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusting formulas by copying across cells w/o changing the re
That didn't work. Maybe because the actual cells are different? the actual
cells are C30:G30 instead of the A1:A3 like I posted before. Jamie "Sean Timmons" wrote: In a blank cell, type: =ROUND(INDIRECT("A1"),-2) and paste across. "Jamie" wrote: Hello, I was wondering if the below situation could be made easier: I have cells that already have a formula in them. The formulas have no specific pattern. Therefore I can't just adjust one formula and copy that across because the referenced cells will change. I need to adjust that formula for all the cells but the reference need to stay the same. For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3 I need to round the answers so I want the cell formulas to look like this: A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc... Is there anyway to add the "round" formula, or any formula for that matter, to one cell and then copy it to the remaining cells so that just the "round" formula is added and all the references stay exactly the same. Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusting formulas by copying across cells w/o changing the refere
Jamie,
The following code will add the ROUND function to any formula resulting with a number within a contiguous range. Tested but I recommend saving the file before running. Insert the code into a standard module: press Alt-[F11] to display the VB editor. In the left pane, right-click on the file name and select Insert Module Paste the code into the new module Modify these two lines to fit your needs: Set rng = Worksheets("Sheet1").Range("A1:B3") d = 2 'set number of decimals to round to. Change the sheet name, range and decimal number as needed. (Leave the "quotes" in place) To run the macro from the worksheet, press Alt-[F8] and select "addround" then click Run. Again, Save the file before running for there is no undo for this. To make sure that the code is not run again, delete it from the module or remove the module. From VB editor right-click on the module and select "Remove..." Regards, Dave -------------------------------------------------------- Option Explicit Sub addround() Dim rng As Range Dim d As Double Dim rLoopCell As Range ' Set Your sheet name and contiguous range here Set rng = Worksheets("Sheet1").Range("A1:B3") d = 2 'set number of decimals to round to. On Error Resume Next For Each rLoopCell In rng If rLoopCell.HasFormula And IsNumeric(rLoopCell.Value) Then rLoopCell = "=" & "ROUND(" & Mid(rLoopCell.Formula, 2, 1000) & "," & d & ")" End If Next rLoopCell End Sub ------------------------------------------------------ "Jamie" wrote in message ... Hello, I was wondering if the below situation could be made easier: I have cells that already have a formula in them. The formulas have no specific pattern. Therefore I can't just adjust one formula and copy that across because the referenced cells will change. I need to adjust that formula for all the cells but the reference need to stay the same. For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3 I need to round the answers so I want the cell formulas to look like this: A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc... Is there anyway to add the "round" formula, or any formula for that matter, to one cell and then copy it to the remaining cells so that just the "round" formula is added and all the references stay exactly the same. Thanks, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusting formulas by copying across cells w/o changing the re
Hi,
Here is a shorter version of Bassman's post Sub addround() Dim cell As Range For Each cell In Selection If cell.HasFormula And IsNumeric(cell) Then cell = "=ROUND(" & Mid(cell.Formula, 2, 1000) & ",2)" End If Next cell End Sub -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Jamie" wrote: That didn't work. Maybe because the actual cells are different? the actual cells are C30:G30 instead of the A1:A3 like I posted before. Jamie "Sean Timmons" wrote: In a blank cell, type: =ROUND(INDIRECT("A1"),-2) and paste across. "Jamie" wrote: Hello, I was wondering if the below situation could be made easier: I have cells that already have a formula in them. The formulas have no specific pattern. Therefore I can't just adjust one formula and copy that across because the referenced cells will change. I need to adjust that formula for all the cells but the reference need to stay the same. For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3 I need to round the answers so I want the cell formulas to look like this: A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc... Is there anyway to add the "round" formula, or any formula for that matter, to one cell and then copy it to the remaining cells so that just the "round" formula is added and all the references stay exactly the same. Thanks, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusting formulas by copying across cells w/o changing the re
This didn't work. I have zero experience with VB so my guess is that I'm
doing something wrong. Is there another way? If not could you try and explain it in more detail. For example when you say past the code I am unsure what code you are referring to. Thanks "Bassman62" wrote: Jamie, The following code will add the ROUND function to any formula resulting with a number within a contiguous range. Tested but I recommend saving the file before running. Insert the code into a standard module: press Alt-[F11] to display the VB editor. In the left pane, right-click on the file name and select Insert Module Paste the code into the new module Modify these two lines to fit your needs: Set rng = Worksheets("Sheet1").Range("A1:B3") d = 2 'set number of decimals to round to. Change the sheet name, range and decimal number as needed. (Leave the "quotes" in place) To run the macro from the worksheet, press Alt-[F8] and select "addround" then click Run. Again, Save the file before running for there is no undo for this. To make sure that the code is not run again, delete it from the module or remove the module. From VB editor right-click on the module and select "Remove..." Regards, Dave -------------------------------------------------------- Option Explicit Sub addround() Dim rng As Range Dim d As Double Dim rLoopCell As Range ' Set Your sheet name and contiguous range here Set rng = Worksheets("Sheet1").Range("A1:B3") d = 2 'set number of decimals to round to. On Error Resume Next For Each rLoopCell In rng If rLoopCell.HasFormula And IsNumeric(rLoopCell.Value) Then rLoopCell = "=" & "ROUND(" & Mid(rLoopCell.Formula, 2, 1000) & "," & d & ")" End If Next rLoopCell End Sub ------------------------------------------------------ "Jamie" wrote in message ... Hello, I was wondering if the below situation could be made easier: I have cells that already have a formula in them. The formulas have no specific pattern. Therefore I can't just adjust one formula and copy that across because the referenced cells will change. I need to adjust that formula for all the cells but the reference need to stay the same. For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3 I need to round the answers so I want the cell formulas to look like this: A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc... Is there anyway to add the "round" formula, or any formula for that matter, to one cell and then copy it to the remaining cells so that just the "round" formula is added and all the references stay exactly the same. Thanks, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adjusting formulas by copying across cells w/o changing the re
that did the trick. Thanks. If there is a shorter/easier way then please let
me know Thanks "Shane Devenshire" wrote: Hi, Here is a shorter version of Bassman's post Sub addround() Dim cell As Range For Each cell In Selection If cell.HasFormula And IsNumeric(cell) Then cell = "=ROUND(" & Mid(cell.Formula, 2, 1000) & ",2)" End If Next cell End Sub -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Jamie" wrote: That didn't work. Maybe because the actual cells are different? the actual cells are C30:G30 instead of the A1:A3 like I posted before. Jamie "Sean Timmons" wrote: In a blank cell, type: =ROUND(INDIRECT("A1"),-2) and paste across. "Jamie" wrote: Hello, I was wondering if the below situation could be made easier: I have cells that already have a formula in them. The formulas have no specific pattern. Therefore I can't just adjust one formula and copy that across because the referenced cells will change. I need to adjust that formula for all the cells but the reference need to stay the same. For example: cell A1: =g25*f2 A2: = z1*aa5 A3: =b2/b3 I need to round the answers so I want the cell formulas to look like this: A1:=round(g25*f2,-2) A2: =round(z1*aa5,-2) etc... Is there anyway to add the "round" formula, or any formula for that matter, to one cell and then copy it to the remaining cells so that just the "round" formula is added and all the references stay exactly the same. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a range of data without adjusting formulas | Excel Discussion (Misc queries) | |||
Copying formulas without changing the range | Excel Discussion (Misc queries) | |||
copying formulas but changing the column # | Excel Discussion (Misc queries) | |||
Copying and adjusting formulas | Excel Worksheet Functions | |||
How copy a range o cells without adjusting formulas? | Excel Discussion (Misc queries) |