Search code examples
excelvba

Bulk replace (range.replace) the content "forcing" tic (') in a range

I've got an Excel range with formulae which are entered as strings. I'll then do a series of range.replaces to modify the "variables" in each formula with the appropriate cell reference. If it matters, I'm doing it this way (well, partly just to see if it will work :)) because the input file may have various columns in different arrangements (COLA, COLB, COLC in one, but COLC, COLA, COLB in another (and so on). Here is an example of one of the formula strings;

'=IF(ISERROR(SUBSTITUTE({C}," ","")),"",SUBSTITUTE(SUBSTITUTE({C}," ",", ",LEN({C})-LEN(SUBSTITUTE({C}," ",""))),",,",","))

I've used a "force" text content character (single tic ') in the first position so the formula is not evaluated at this stage.

All of the assignment and substitutions are working and the formula are ready to be used. But I can't get a range.replace to replace the text force tic (col 1) to remove the tic so the formula can be used as a formula.

Is there a way to do this in bulk? I'd prefer to not use a loop - it would be slower than a simple range.replace, but ...

Thank you

Solution

  • Use value transfer, which will remove the '.

    If your formulas are in contiguous cells:

    With ThisWorkbook.Worksheets("yourworksheet").Range("A1:C1")
    .Formula = .Value
End With

    If your formulas are not in contiguous cells:

    Dim rngWithFormulas As Range, area As Range
Set rngWithFormulas = ThisWorkbook.Worksheets("yourworksheet").Range("A1:C1,D4:E5")

For Each area In rngWithFormulas.Areas
    With area
        .Formula = .Value
    End With
Next