For Each Cell in Range

[pth]

Hi,

Unfortunately when I moved jobs I lost a load of vba I had to speed up day to day workflow, I have a number of small tools that essentially 'modified' the formula that was in the existing cell(s).

I have placed an example of one below, but the bit I'm struggling with as don't use VBA as much as I used to is how I can make this tool apply it to all the cells in the currently highlighted range;
The range may be multiple columns / rows so it can't just loop down or across.

Sub Formula_Iserror()
MyOrigFormula = ActiveCell.Formula
MyFormulaPrefix = "=Iferror("
MyFormulaSuffix = ",0)"
MyOrigFormulaLen = Len(MyOrigFormula)
MyOrigFormula2 = Right(ActiveCell.Formula, MyOrigFormulaLen - 1)
MyNewFormula = MyFormulaPrefix & MyOrigFormula2 & MyFormulaSuffix
ActiveCell.Formula = MyNewFormula
End Sub



Any help really appreciated as its really slowing me down ;<

 

[Eric W]

Welcome to the forum!

If all the formulas in the range are the same, then this should work:

    Selection.Formula = "=IFERROR(" & Mid(Selection(1, 1).Formula, 2) & ",0)"

If they are different, then try this:

    For Each c In Selection
        c.Formula = "=IFERROR(" & Mid(c.Formula, 2) & ",0)"
    Next c

 

[pth]

Perfect thanks Eric !! Such a small line of code will make so much difference to my working day !
My previous macro was longer than this and that just makes it so simple !!

 

[Eric W]

Glad it works for you!

 

[pth]

I've just found out that it doesn't quite work, looks like the code will replicate the formula from the active cell to all other cells below it for example (but increments the row number).
However if I have formulae that are inconsistent in the range then it effectively changes the formula.

Is there a way I can just create a loop that will loop from the active cell through the range of cells I have selected and just apply my changes to each individual cell as it loops through, ie. picking up the new formula in each cell as it goes along ?

thanks a lot !

 

[Lukasier]

You can use
dim MyRange as Range
set MyRange = Application.InputBox("",,,,,,,8)

and for each cell in MyRange you could apply your macro. This will pop up input box where you will choose your range. Will that work for you?

 

[pth]

Hi thanks, I'm just wanting to the macro to directly apply to the range that is already set. The bit I'm struggling with is just making the macro to loop through all the cells that are in the currently selected range.

 

[pth]

Sorry to be really clear the bit im stuggling with is the 'for each cell in range' how I make this loop work so it loops through each cell in the currently selected range - thanks

 

[Lukasier]

Dim MyRng as Range
Dim Cell as Range

set MyRng = Application.InputBox("Choose range","Range",,,,,,8)
'Now you might choose your range

for each Cell in MyRng

WHATEVER YOU WANT TO DO WITHIN EVERY CELL

next Cell

 

[pth]

Thanks a lot, think I've managed to figure it out albeit may be bit long winded but ended up with this as final code, any other pointers appreciated :-

Sub Formula_Iserror()
On Error GoTo Error99
' Change Formula Prefix & Suffix Here
MyFormulaPrefix = "=Iferror("
MyFormulaSuffix = ",0)"

MyFirstCellRow = ActiveCell.Row
MyFirstCellColumn = ActiveCell.Column
MyRows = Selection.Rows.Count
MyColumns = Selection.Columns.Count
MyLastRow = MyFirstCellRow + MyRows - 1
MyLastColumn = MyFirstCellColumn + MyColumns - 1
Dim cel As range
Dim selectedRange As range
Set selectedRange = Application.Selection
For Each cel In selectedRange.Cells
cel.Select

MyFormula = Selection.Formula
MyLen = Len(MyFormula)
MyTrimFormula = Right(MyFormula, MyLen - 1)
MyNewFormula = MyFormulaPrefix & MyTrimFormula & MyFormulaSuffix
Selection.Formula = MyNewFormula
Next cel
ActiveSheet.range(Cells(MyFirstCellRow, MyFirstCellColumn), Cells(MyLastRow, MyLastColumn)).Select
Exit Sub
Error99:
E = MsgBox("Contact : paulwtodhunter@gmail.com", vbOKOnly, "VBA Error")
End Sub