Adding and subtrating from rates.

G

GaleForce

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! :
 
D

Don Guillett

Just put -.626 in a cell somewhere>copy that cell>hightlight the cells
desired>edit>paste special>add
 
K

keepITcool

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 >
 
G

GaleForce

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
 
S

Steve Hieb

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
 
D

Dave Peterson

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)
 
G

GaleForce

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
 
C

Chris_Fifield

GaleForce said:
*I kind of understand that, but how do I put a formula in to work i
the background? I don't just put it in the cell as data, do I? *
Click to expand...

To change all your rates based on a single cell just replace thos
rates, typed as simple numbers, with formulas that include the rate an
the referenced cell.

For example:

=6.875+$A$1

where cell A1 contains your rate differential.

If you want to get really fancy you can add a spinner control t
manipulate the 'differential' cell. (This would require one cell t
manipulate, with increments of 1, and a cell that your formulae wil
reference containing the spinner-manipulated value divided by 1000
 
D

Dave Peterson

Try that little procedure on a test worksheet/range.

You'll see your values have been converted to formulas--so you're done!

If you want the original values, put 0 in A1 (or leave it empty).
 
Top