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

30
2014-03

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

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

Related Answers

PileOfMush

If your header is in cell A1 and your formulas need to start in A2 and go to the end, start by selecting A2, press (and release) "End" then press "Shift+Down Arrow". Now everything from A2 to the end is selected and you can paste your formula. If you have no data in A2-A65535, you will select the entire range. If you have data, this will only select to the end of the list of data.

AdamV

I think you may have to reword this a little for people to understand exactly what you are after. Are you trying to reference the range of all cells in a column except for the header? Most likely you can do this using an OFFSET with a COUNTA function inside, for example to get all of column A without the header you could use:

=OFFSET($A$1,1,0,COUNTA(A:A),1)

This assumes all the other cells in A have something in up to the end of the data range, no blanks in between.

What function are you trying to point at this range?

Siamak

for example if you want to apply SIN(x) on a set of data in a row, in a vacant cell type: =SIN("click on the first cell of the row") then press "enter" then grab the handle of this cell and draw it up to the last cell. now you have a set of data in a row each cell of which is has the value of the SIN(the original cell)

Home

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