Adding and subtrating from rates.
 

Hey, got a question for the experts. I have an excel spreadsheet of
bunch of rates, an example of one would be 6.875. I need to have
field at the top or anywhere really where a user could input a numbe
such as "+.075" or "-.626" or anything like that and it will take tha
value and add or subtract it to every value in the sheet. Any hel
would be appreciated. Thanks! :

--
Message posted from http://www.ExcelForum.com

Adding and subtrating from rates.
 

Just put -.626 in a cell somewherecopy that cellhightlight the cells
desirededitpaste specialadd
--
Don Guillett
SalesAid Software

"GaleForce " wrote in message
...
Hey, got a question for the experts. I have an excel spreadsheet of a
bunch of rates, an example of one would be 6.875. I need to have a
field at the top or anywhere really where a user could input a number
such as "+.075" or "-.626" or anything like that and it will take that
value and add or subtract it to every value in the sheet. Any help
would be appreciated. Thanks! :)


---
Message posted from http://www.ExcelForum.com/

Adding and subtrating from rates.
 

try this..
please edit range refences..
[mycell] could be replaced with activecell
[mycells] could be replaced with activesheet.cells

Sub Adjust()
[myCell].Copy
[myCells] _
.SpecialCells(xlCellTypeConstants, xlNumbers) _
.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


GaleForce wrote:

Hey, got a question for the experts. I have an excel spreadsheet of a
bunch of rates, an example of one would be 6.875. I need to have a
field at the top or anywhere really where a user could input a number
such as "+.075" or "-.626" or anything like that and it will take that
value and add or subtract it to every value in the sheet. Any help
would be appreciated. Thanks! :)


---
Message posted from http://www.ExcelForum.com/

Adding and subtrating from rates.
 

Problem is that I'm a complete noob at excel when it comes to advance
things like this. What I was told to do was very specific, that being
field that someone could enter a value, hit submit or enter, and the
have the new value flooded into the spreadsheet

--
Message posted from http://www.ExcelForum.com

Adding and subtrating from rates.
 

How about something like this:

Sub RunAdjProcess()
AdjRange Range("YourNamedRange"), 0.075
End Sub

Sub AdjRange(TargetRng As Range, AdjVal As Double)
Dim C As Range
Dim CurrentFormula As String
For Each C In TargetRng
If IsEmpty(C) = False Then
If IsNumeric(C.Value) Then
If C.HasFormula Then
CurrentFormula = C.Formula
C.Formula = "=(" & Mid(CurrentFormula, 2, 500) _
& ")+" & CStr(AdjVal)
Else
C.Value = C.Value + AdjVal
End If
End If
End If
Next
End Sub

You could trigger this to run via a button or perhaps the
Worksheet_Change event within the worksheet object. Didn't test this
much, but should work for most scenarios. Note, it appends to any
formula that may be in the range of values your adjusting. Otherwise
it could be much simplier.

HTH,
Steve Hieb

Adding and subtrating from rates.
 

Where do I put this code

--
Message posted from http://www.ExcelForum.com

Adding and subtrating from rates.
 

One way:

I'm gonna use A1 as my cell to hold that +/- value.
Find another cell (any unused cell) and put this formula:
=$a$1

Now select your range that should react to this cell. As big as you want!

Edit|paste special|and check Add.

Notice that all your formulas now have +($a$1) after them. And all the values
have changed to formulas that include +($a$1) at the end.

Now go back and wipe out that helper cell (the one with the formula =$a$1)




"GaleForce <" wrote:

Hey, got a question for the experts. I have an excel spreadsheet of a
bunch of rates, an example of one would be 6.875. I need to have a
field at the top or anywhere really where a user could input a number
such as "+.075" or "-.626" or anything like that and it will take that
value and add or subtract it to every value in the sheet. Any help
would be appreciated. Thanks! :)

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Adding and subtrating from rates.
 

I kind of understand that, but how do I put a formula in to work in th
background? I don't just put it in the cell as data, do I

--
Message posted from http://www.ExcelForum.com