Answers: How to update all functions in Excel 2007?

Jonno_FTW

Is there a way to quickly fill down a column of functions. For example, if I change my function from

=3*B2

And then fill down so it does it for a lot of rows.

But then change this column to:

=10*B2

Is there a way to quickly update all my functions in the column?

Craig

Update your formula in the first cell. Position you mouse over the bottom-right hand corner of the cell, the cursor which change from a white-cross to a black-cross. Double-click and your range will auto-fill with your new formula.

Lance Roberts

Drag and Fill Again.

If this is a common occurrence you can used Named Constants for the constant value, so you can change it easy.

David Basarab

You can do a copy and replace.

So highlight the cells you want to change, and do a Ctrl-F and replace the value according.

However this might have side effects you might not desire.

For example lets say you want to change 3 to 10, then you might actually change cell B3 to B10, B30 to B100 etc. . .

Your best bet is to make a constant by having all the values represented in another cell.

For exmaple A1 then your formula will look like

$A$1 * B1
$A$1 * B2
$A$1 * B3

etc.. .

Then you just change A1 and all your forumlas update accordingly.

($ means don't change the cell either column or row after when I do a drag or fill. In this example A and 1 do not change.)

microsoft excel - How to change a function/formula in a whole column where I also have empty cells

microsoft-excel microsoft-excel-2007 vba worksheet-function

Zvi

I have a column with a formula/function that has other cells as parameters. However in the column, every few rows I have a subtotal and blanks, for readability. Now I decided to change the formula/function but I can not do copy+paste on the whole column because of the subtotals/blank cells, and I can not do change+replace because there are parameters in the functions that uses other cells and thus it is different in each cell in the column.

For example, if the formula is =if(A5>24,1,"") and I want to change it to =AA_userfn(A5,B5)

Any idea how to change it easily? currently I copy+paste few rows at a time, but that is tedious.

Maybe a Sub that will go all over the column (the column number as an input parameter) and looks for =if(, and then replaces it with =aa_userfn(Ax, By) where Ax and By are calculated based on the position of the cell currently changed in reference to the previous values in that cell.

Any ideas will be welcomed

Related Answers

Matt

Change to suit your needs

Public Sub UpdateFormula()
Dim r As Long, c As Long, n As Long, x As Long, a As Long
Dim lRow As Long
Dim rngSlct As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Select the column you want to update. This can be multiple columns
Set rngSlct = Selection
With rngSlct
    x = .Columns.count
'Loop through all the selected columns
For n = 1 To x
    a = rngSlct(1, n).Column
lRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).row
c = a

With ActiveCell
    For r = ActiveCell.row To lRow
        If Left(Cells(r, c).Formula= "=If(" Then
            Cells(r, c).Formula = 'Type your new formula here
        End If
    Next r
End With

Next n
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Home

How to update all functions in Excel 2007?